{"id":19358,"date":"2025-07-24T13:25:32","date_gmt":"2025-07-24T07:55:32","guid":{"rendered":"https:\/\/meetanshi.com\/blog\/?p=19358"},"modified":"2025-09-08T16:19:46","modified_gmt":"2025-09-08T10:49:46","slug":"connect-magento-2-to-google-sheets","status":"publish","type":"post","link":"https:\/\/meetanshi.com\/blog\/connect-magento-2-to-google-sheets\/","title":{"rendered":"How to Connect Magento 2 to Google Sheets?"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Google Sheets makes it easy for you to manage your data.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Manually adding Magento 2 data into Google Sheets is too time-consuming and messy. In this blog, I will show you how to connect Magento 2 to Google Sheets.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let\u2019s dive in.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Why Connect Google Sheets to Magento 2?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">As Google Sheet helps to maintain your Magento 2 data effectively. It also allows you to:&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Keep your data updated from time to time&nbsp;<\/li>\n\n\n\n<li>Improve reporting and make communication effective<\/li>\n\n\n\n<li>Make your operations easy and fast<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">This integration brings about significant changes in how you conduct real-time reporting. Additionally, you can also manage bulk editing and modification, which is accessible to all teammates.&nbsp;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Methods to Export Magento 2 to Google Sheets<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">There are a few different ways to connect Magento 2 to Google Sheets. Few of them are super easy, while others give you more control over exporting if you are comfortable with coding.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here are 4 methods of how to integrate Magento and Google Sheets:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Use Extension<\/strong>: It&#8217;s one of the simplest ways. You just need to install a ready-to-use extension that exports your data directly to Google Sheets.<\/li>\n\n\n\n<li><strong>ETL Platforms:<\/strong> ETL stands for Extract, Transform &amp; Load. These platforms help to move data between apps automatically without coding.&nbsp;<\/li>\n\n\n\n<li><strong>Export Manually:<\/strong> You can export the data from Magento to a Google sheet manually. It gives you flexibility and it&#8217;s great for quick tasks.&nbsp;<\/li>\n\n\n\n<li><strong>Google App Script:<\/strong> If you know technical skills and coding, then you can use this method. It allows you to create a custom setup that automatically exports the data to the sheets. This method gives you full control over the exporting process.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">We will explore each of the above-mentioned methods in detail in the following sections, so you can choose the one that works best for you.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to Use Meetanshi SQL Report Builder to Automate Magento 2 to Google Sheets Exports?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If you are looking for a reliable and easy option to export Magento 2 data to Google Sheets, the Meetanshi SQL Report Builder is a great choice.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It doesn\u2019t require any coding skills and it works very smoothly. Even non-tech persons can use it easily.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here are the steps to export your data.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Install and Enable the Extension&nbsp;<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Firstly, install the <a href=\"https:\/\/meetanshi.com\/magento-2-sql-report-builder.html\">Meetanshi SQL Report Builder<\/a> extension on your Magento 2 store. Then, you need to enable the extension.&nbsp;<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1686\" height=\"606\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/1-Magento-2-SQL-Report-Builder.png\" alt=\"Magento 2 SQL Report Builder\" class=\"wp-image-19404\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/1-Magento-2-SQL-Report-Builder.png 1686w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/1-Magento-2-SQL-Report-Builder-250x90.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/1-Magento-2-SQL-Report-Builder-700x252.png 700w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/1-Magento-2-SQL-Report-Builder-768x276.png 768w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/1-Magento-2-SQL-Report-Builder-1536x552.png 1536w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/1-Magento-2-SQL-Report-Builder-403x145.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/1-Magento-2-SQL-Report-Builder-964x346.png 964w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/1-Magento-2-SQL-Report-Builder-120x43.png 120w\" sizes=\"auto, (max-width: 1686px) 100vw, 1686px\" \/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">Step 2: Add a new SQL report<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Go to the admin panel, click on SQL Report Builder &gt; Manage SQL Report. Now, add a new SQL Report.&nbsp;<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"924\" height=\"720\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/2-Add-a-New-SQL-Report.png\" alt=\"Add a New SQL Report\" class=\"wp-image-19402\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/2-Add-a-New-SQL-Report.png 924w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/2-Add-a-New-SQL-Report-250x195.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/2-Add-a-New-SQL-Report-700x545.png 700w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/2-Add-a-New-SQL-Report-768x598.png 768w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/2-Add-a-New-SQL-Report-403x314.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/2-Add-a-New-SQL-Report-120x94.png 120w\" sizes=\"auto, (max-width: 924px) 100vw, 924px\" \/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">Step 3: Define SQL Query<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Now, as per your requirements, add the SQL query to get the required data. For example, here we run a query to get the number of new customers by month.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"916\" height=\"544\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/3-Define-SQL-Query.png\" alt=\"Define SQL Query\" class=\"wp-image-19408\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/3-Define-SQL-Query.png 916w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/3-Define-SQL-Query-250x148.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/3-Define-SQL-Query-700x416.png 700w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/3-Define-SQL-Query-768x456.png 768w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/3-Define-SQL-Query-403x239.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/3-Define-SQL-Query-120x71.png 120w\" sizes=\"auto, (max-width: 916px) 100vw, 916px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Then, click <strong>Execute SQL Query<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If the results look good, set the <strong>Generate Report on Schedule <\/strong>option to <strong>Yes<\/strong> and set the Refresh Frequency as per your requirements.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Then <strong>Save<\/strong> the report.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Now, in the reports grid, click the CSV button against the created report to copy its URL.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"903\" height=\"430\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/4-Click-the-CSV-Button-Against-the-Created-Report-to-Copy-its-URL.png\" alt=\"Click the CSV Button Against the Created Report to Copy its URL\" class=\"wp-image-19396\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/4-Click-the-CSV-Button-Against-the-Created-Report-to-Copy-its-URL.png 903w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/4-Click-the-CSV-Button-Against-the-Created-Report-to-Copy-its-URL-250x119.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/4-Click-the-CSV-Button-Against-the-Created-Report-to-Copy-its-URL-700x333.png 700w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/4-Click-the-CSV-Button-Against-the-Created-Report-to-Copy-its-URL-768x366.png 768w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/4-Click-the-CSV-Button-Against-the-Created-Report-to-Copy-its-URL-403x192.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/4-Click-the-CSV-Button-Against-the-Created-Report-to-Copy-its-URL-120x57.png 120w\" sizes=\"auto, (max-width: 903px) 100vw, 903px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Go to <strong>Google Sheets <\/strong>and click use the copied URL with the following function to get the live data from Magento 2 to Google Sheets:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>=IMPORTDATA(\u201cthe_copied_url_here\u201d)<\/em><\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"947\" height=\"165\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/5-Go-to-Google-Sheets-and-Click-Use-the-Copied-URL-with-Following-Function.png\" alt=\"Go to Google Sheets and Click Use the Copied URL with Following Function\" class=\"wp-image-19418\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/5-Go-to-Google-Sheets-and-Click-Use-the-Copied-URL-with-Following-Function.png 947w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/5-Go-to-Google-Sheets-and-Click-Use-the-Copied-URL-with-Following-Function-250x44.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/5-Go-to-Google-Sheets-and-Click-Use-the-Copied-URL-with-Following-Function-700x122.png 700w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/5-Go-to-Google-Sheets-and-Click-Use-the-Copied-URL-with-Following-Function-768x134.png 768w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/5-Go-to-Google-Sheets-and-Click-Use-the-Copied-URL-with-Following-Function-403x70.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/5-Go-to-Google-Sheets-and-Click-Use-the-Copied-URL-with-Following-Function-120x21.png 120w\" sizes=\"auto, (max-width: 947px) 100vw, 947px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Now, it will load the data from the Magento 2 report.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"400\" height=\"296\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/6-Formula-Will-Load-Data-From-the-Magento-2-Report.png\" alt=\"Formula Will Load Data From the Magento 2 Report\" class=\"wp-image-19398\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/6-Formula-Will-Load-Data-From-the-Magento-2-Report.png 400w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/6-Formula-Will-Load-Data-From-the-Magento-2-Report-250x185.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/6-Formula-Will-Load-Data-From-the-Magento-2-Report-120x89.png 120w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Now, you have direct integration from Magento 2 to Google Sheets using the SQL Report Builder extension. Further, since we have set up automatic refreshes, the data in Google Sheets will auto-update without any manual steps.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You can follow similar steps to automate data from your store to Google Sheets for building reports.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Manually Export Magento 2 to Google Sheets<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/meetanshi.com\/blog\/magento-2-export-products-into-csv\/\">Exporting data manually<\/a> is also a good method. Magento has an in-built feature for this.If you have a small database, you can use this method to export your Magento 2 data to Google Sheets.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In your Magento 2 admin, go to the <strong>System &gt; Data Transfer &gt; Export<\/strong>.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"608\" height=\"639\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/7-In-Your-Magento-2-Admin-Go-to-the-System-Data-Transfer-Export.png\" alt=\"In Your Magento 2 Admin Go to the System Data Transfer Export\" class=\"wp-image-19414\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/7-In-Your-Magento-2-Admin-Go-to-the-System-Data-Transfer-Export.png 608w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/7-In-Your-Magento-2-Admin-Go-to-the-System-Data-Transfer-Export-250x263.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/7-In-Your-Magento-2-Admin-Go-to-the-System-Data-Transfer-Export-403x424.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/7-In-Your-Magento-2-Admin-Go-to-the-System-Data-Transfer-Export-120x126.png 120w\" sizes=\"auto, (max-width: 608px) 100vw, 608px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Choose the <strong>Entity Type <\/strong>(for e.g., Products) and select CSV as format.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"694\" height=\"294\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/8-Choose-the-Entity-Type-and-Select-CSV-as-Format.png\" alt=\"Choose the Entity Type and Select CSV as Format\" class=\"wp-image-19400\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/8-Choose-the-Entity-Type-and-Select-CSV-as-Format.png 694w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/8-Choose-the-Entity-Type-and-Select-CSV-as-Format-250x106.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/8-Choose-the-Entity-Type-and-Select-CSV-as-Format-403x171.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/8-Choose-the-Entity-Type-and-Select-CSV-as-Format-120x51.png 120w\" sizes=\"auto, (max-width: 694px) 100vw, 694px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Select the attributes you want to export into CSV and click <strong>Continue<\/strong>.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"835\" height=\"640\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/9-Select-the-Attributes-You-Want-to-Export-into-CSV-and-Click-Continue.png\" alt=\"Select the Attributes You Want to Export into CSV and Click Continue\" class=\"wp-image-19410\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/9-Select-the-Attributes-You-Want-to-Export-into-CSV-and-Click-Continue.png 835w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/9-Select-the-Attributes-You-Want-to-Export-into-CSV-and-Click-Continue-250x192.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/9-Select-the-Attributes-You-Want-to-Export-into-CSV-and-Click-Continue-700x537.png 700w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/9-Select-the-Attributes-You-Want-to-Export-into-CSV-and-Click-Continue-768x589.png 768w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/9-Select-the-Attributes-You-Want-to-Export-into-CSV-and-Click-Continue-403x309.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/9-Select-the-Attributes-You-Want-to-Export-into-CSV-and-Click-Continue-120x92.png 120w\" sizes=\"auto, (max-width: 835px) 100vw, 835px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Once you have the CSV file, you can import it into Google Sheets manually. But this method does not offer any automation feature; i.e. you will need to perform manual export-imports every time you need fresh data.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Google App Script Code to Get Magento 2 Data<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If you know coding, Google App Script can help you automate Magento 2 export to Google Sheets without doing it manually every time.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This method gives you full control over exporting data. Here are the steps to perform this;<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Enable Magento 2 API<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Log in to Magento Admin: Go to <strong>System &gt; Integrations <\/strong>and click <strong>Add New Integration<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Fill in the required details like<strong> Name<\/strong> (e.g., &#8220;Google Apps Script Export&#8221;), email, etc.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Go to the API tab.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Under <strong>Resource Access<\/strong>, select the data you want to export. For example, to export products, choose <strong>Catalog -&gt; Products -&gt; All<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Click Save.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"641\" height=\"408\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/10-Under-Resource-Access-Select-the-Data-You-Want-to-Export.png\" alt=\"Under Resource Access Select the Data You Want to Export\" class=\"wp-image-19406\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/10-Under-Resource-Access-Select-the-Data-You-Want-to-Export.png 641w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/10-Under-Resource-Access-Select-the-Data-You-Want-to-Export-250x159.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/10-Under-Resource-Access-Select-the-Data-You-Want-to-Export-403x257.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/10-Under-Resource-Access-Select-the-Data-You-Want-to-Export-120x76.png 120w\" sizes=\"auto, (max-width: 641px) 100vw, 641px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Find your new integration in the list and click <strong>Activate<\/strong>. Confirm the required permissions and you\u2019ll get the integration tokens.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"866\" height=\"511\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/11-Find-YourNew-Integration-in-the-List-and-Click-Activate.png\" alt=\"Find Your New Integration in the List and Click Activate\" class=\"wp-image-19416\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/11-Find-YourNew-Integration-in-the-List-and-Click-Activate.png 866w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/11-Find-YourNew-Integration-in-the-List-and-Click-Activate-250x148.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/11-Find-YourNew-Integration-in-the-List-and-Click-Activate-700x413.png 700w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/11-Find-YourNew-Integration-in-the-List-and-Click-Activate-768x453.png 768w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/11-Find-YourNew-Integration-in-the-List-and-Click-Activate-403x238.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/11-Find-YourNew-Integration-in-the-List-and-Click-Activate-120x71.png 120w\" sizes=\"auto, (max-width: 866px) 100vw, 866px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Note down these details, which will be later used to authenticate the API requests to get data.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Use Magento 2 API in Google Apps Script<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Open the Google Sheet where you want to get the Magento data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Click <strong>Extensions &gt; Apps Script<\/strong>. A new browser tab will open with the Apps Script editor. This is your code editor.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"406\" height=\"260\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/12-Use-Magento-2-API-in-Google-Apps-Script.png\" alt=\"Use Magento 2 API in Google Apps Script\" class=\"wp-image-19394\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/12-Use-Magento-2-API-in-Google-Apps-Script.png 406w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/12-Use-Magento-2-API-in-Google-Apps-Script-250x160.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/12-Use-Magento-2-API-in-Google-Apps-Script-403x258.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/12-Use-Magento-2-API-in-Google-Apps-Script-120x77.png 120w\" sizes=\"auto, (max-width: 406px) 100vw, 406px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Delete any default code (function myFunction() { &#8230; }) and paste the following JavaScript code into the editor.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">\/\/ --- CONFIGURATION ---\nconst MAGENTO_BASE_URL = \"https:\/\/your-magento-domain.com\/rest\/V1\/\"; \/\/ REPLACE with your Magento URL (must end with \/rest\/V1\/)\nconst MAGENTO_ACCESS_TOKEN = \"YOUR_MAGENTO_ACCESS_TOKEN\";       \/\/ REPLACE with your token from Magento (Part 1)\nconst MAGENTO_API_ENDPOINT = \"products\";                        \/\/ REPLACE with the API endpoint (e.g., \"products\", \"customers\", \"orders\")\nconst WORKSHEET_NAME = \"Magento Export Data\";                   \/\/ REPLACE with your desired sheet\/tab name in Google Sheets\nconst PAGE_SIZE = 100;                                          \/\/ Number of items per API request (Magento max is often 100)\n\n\/\/ --- Core Function to Fetch Data from Magento API (with Pagination) ---\nfunction fetchMagentoData() {\n  const allItems = [];\n  let currentPage = 1;\n  let totalCount = -1; \/\/ -1 ensures the first fetch happens\n\n  const headers = {\n    'Authorization': 'Bearer ' + MAGENTO_ACCESS_TOKEN,\n    'Content-Type': 'application\/json'\n  };\n\n  try {\n    while (totalCount === -1 || allItems.length &lt; totalCount) {\n      \/\/ Construct URL for the current page\n      const url = `${MAGENTO_BASE_URL}${MAGENTO_API_ENDPOINT}?searchCriteria[currentPage]=${currentPage}&amp;searchCriteria[pageSize]=${PAGE_SIZE}`;\n      Logger.log('Fetching from: ' + url);\n\n      const options = {\n        'method': 'GET',\n        'headers': headers,\n        'muteHttpExceptions': true \/\/ Critical: Allows us to read response code for errors\n      };\n\n      const response = UrlFetchApp.fetch(url, options);\n      const responseCode = response.getResponseCode();\n      const responseBody = response.getContentText();\n\n      if (responseCode >= 200 &amp;&amp; responseCode &lt; 300) {\n        const data = JSON.parse(responseBody);\n        const items = data.items || []; \/\/ Magento API often returns data in 'items' array\n\n        if (totalCount === -1) {\n          totalCount = data.total_count || items.length; \/\/ Set totalCount on first successful fetch\n          Logger.log(`Total estimated items: ${totalCount}`);\n        }\n\n        if (items.length === 0 &amp;&amp; currentPage > 1) { \/\/ If no items on subsequent pages, we're done\n          break;\n        }\n        if (items.length === 0 &amp;&amp; currentPage === 1 &amp;&amp; totalCount === 0) { \/\/ No items at all\n            break;\n        }\n\n        allItems.push(...items); \/\/ Add fetched items to the main array\n        currentPage++;\n\n        \/\/ Log progress for large fetches\n        Logger.log(`Fetched ${allItems.length} of ${totalCount} items so far.`);\n\n      } else {\n        \/\/ Handle HTTP errors (4xx, 5xx)\n        const errorMessage = `API Error - Code: ${responseCode}, Message: ${responseBody}`;\n        Logger.log(errorMessage);\n        SpreadsheetApp.getUi().alert('API Error', 'Failed to fetch data from Magento. Check Apps Script logs (Ctrl+Enter) for details. Error: ' + responseBody, SpreadsheetApp.getUi().ButtonSet.OK);\n        return null; \/\/ Return null on error\n      }\n    }\n    Logger.log('Total items fetched: ' + allItems.length);\n    return allItems;\n\n  } catch (e) {\n    \/\/ Handle script execution errors\n    Logger.log('Script Exception during fetch: ' + e.toString());\n    SpreadsheetApp.getUi().alert('Script Error', 'An unexpected error occurred during data fetching. Check Apps Script logs (Ctrl+Enter) for details.', SpreadsheetApp.getUi().ButtonSet.OK);\n    return null;\n  }\n}\n\n\/\/ --- Function to Write Data to Google Sheet ---\nfunction writeDataToSheet(data) {\n  if (!data || data.length === 0) {\n    Logger.log('No data to write to sheet.');\n    SpreadsheetApp.getUi().alert('No Data', 'No data was fetched from Magento to write to the sheet.', SpreadsheetApp.getUi().ButtonSet.OK);\n    return;\n  }\n\n  const ss = SpreadsheetApp.getActiveSpreadsheet();\n  let sheet = ss.getSheetByName(WORKSHEET_NAME);\n\n  \/\/ Create sheet if it doesn't exist\n  if (!sheet) {\n    sheet = ss.insertSheet(WORKSHEET_NAME);\n    Logger.log(`Created new sheet: ${WORKSHEET_NAME}`);\n  } else {\n    sheet.clearContents(); \/\/ Clear existing content\n    Logger.log(`Cleared existing data from sheet: ${WORKSHEET_NAME}`);\n  }\n\n  \/\/ Extract all unique headers from all objects\n  const allHeaders = new Set();\n  data.forEach(item => {\n    Object.keys(item).forEach(key => allHeaders.add(key));\n  });\n  const sortedHeaders = Array.from(allHeaders).sort(); \/\/ Optional: Sort headers alphabetically\n\n  const values = [];\n  values.push(sortedHeaders); \/\/ Add headers as the first row\n\n  \/\/ Add data rows\n  data.forEach(item => {\n    const row = sortedHeaders.map(header => {\n      let value = item[header];\n      \/\/ Handle nested objects\/arrays by stringifying them (optional: improve this for specific needs)\n      if (typeof value === 'object' &amp;&amp; value !== null) {\n        return JSON.stringify(value);\n      }\n      return value;\n    });\n    values.push(row);\n  });\n\n  \/\/ Write all data to the sheet\n  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);\n  Logger.log('Data successfully written to Google Sheet.');\n  SpreadsheetApp.getUi().alert('Success!', `Data exported successfully to sheet '${WORKSHEET_NAME}'.`, SpreadsheetApp.getUi().ButtonSet.OK);\n}\n\n\/\/ --- Main Execution Function (Called from Menu) ---\nfunction exportMagentoDataToSheets() {\n  const ui = SpreadsheetApp.getUi();\n  const response = ui.alert(\n    'Confirm Export',\n    'Do you want to fetch and export data from Magento to Google Sheets?',\n    ui.ButtonSet.YES_NO\n  );\n  if (response == ui.Button.YES) {\n    Logger.log('Export process started.');\n    const magentoData = fetchMagentoData();\n    if (magentoData) {\n      writeDataToSheet(magentoData);\n    } else {\n      Logger.log('Data fetching failed or returned no data.');\n    }\n  } else {\n    Logger.log('Export cancelled by user.');\n  }\n}\n\n\/\/ --- Custom Menu Setup ---\nfunction onOpen() {\n  const ui = SpreadsheetApp.getUi();\n  ui.createMenu('Magento Export')\n      .addItem('Export Data to Sheet', 'exportMagentoDataToSheets')\n      .addToUi();\n}\n\n\/\/ Optional: Install menu on script installation too\nfunction onInstall() {\n  onOpen();\n}\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In the above script, make sure to replace the base URL and access token.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Save the code and refresh the Google Sheet.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Click the option in the top menu for <strong>Magento Export &gt; Export Data to Sheet<\/strong>.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"601\" height=\"176\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/13-Click-Magento-Export-Export-Data-to-Sheet.png\" alt=\"Click Magento Export Data to Sheet\" class=\"wp-image-19412\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/13-Click-Magento-Export-Export-Data-to-Sheet.png 601w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/13-Click-Magento-Export-Export-Data-to-Sheet-250x73.png 250w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/13-Click-Magento-Export-Export-Data-to-Sheet-403x118.png 403w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/07\/13-Click-Magento-Export-Export-Data-to-Sheet-120x35.png 120w\" sizes=\"auto, (max-width: 601px) 100vw, 601px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">This will run the app script, call the Magento 2 API, and get the required data from Magento 2 to Google Sheet.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Which Method is Best for You?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">From the above three methods, the best one considering the technical dependency, amount of effort, and the need to generate reports regularly is the <strong>SQL Report Builder<\/strong>. In less time, it does more for you.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The SQL Report Builder comes with an auto-update feature that other methods lack.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Your Magento 2 Google Sheets data always stays updated due to its automatic refreshes feature, saving you time to create a new sheet from scratch whenever the need arises.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Additionally, the Meetanshi SQL Report Builder:&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>doesn&#8217;t need any coding, just a few simple steps<\/li>\n\n\n\n<li>one-time setup for generating regular reports<\/li>\n\n\n\n<li>saves created SQL reports to update the content automatically<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">With simple settings, you can define your SQL queries and export the final output in a CSV file, completely removing the steps to export and edit your CSV file whenever you need fresh data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\r\n\r\n<div class=\"meetanshi-cta\">\r\n<div class=\"cta-content-wrapper\">\r\n<span> Magento 2 SQL Report Builder <\/span>\r\n<p>Goodbye manual reporting forever; get our no-code solution to auto-update your data.<\/p>\r\n<a href=\"https:\/\/meetanshi.com\/magento-2-sql-report-builder.html\" target=\"_blank\" class=\"btn-primary\">Get Now <\/a>\r\n<\/div>\r\n<div class=\"cta-image-new\">\r\n<img decoding=\"async\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/11\/magento-2-sql-report-builder.png\" alt=\"Magento 2 SQL Report Builder\">\r\n<\/div>\r\n<\/div><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Google Sheets makes it easy for you to manage your data.&nbsp; Manually adding Magento 2 data into Google Sheets is too time-consuming and messy. In&#8230;<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[34],"tags":[],"class_list":["post-19358","post","type-post","status-publish","format-standard","hentry","category-magento"],"acf":[],"_links":{"self":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/19358","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/comments?post=19358"}],"version-history":[{"count":11,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/19358\/revisions"}],"predecessor-version":[{"id":21526,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/19358\/revisions\/21526"}],"wp:attachment":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/media?parent=19358"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/categories?post=19358"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/tags?post=19358"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}