Here we will take a look at how to make scheduled exports to Google Sheets from Shopify. You can use this to have always the newest exported information for better and automated report constructing
Steps exporting to Google Sheets
1. Setup export
In the Excelify app select checkboxes with information that you wish to export to Google Sheets. Here we will use product information, but you can also apply this tutorial for any other export combination.
2. Configure export options
Here we will need to do two things – configure our export to run on specific times and set the custom file name for the export. We will need this to ensure that our exported file link is always the same. To do this we click on ‘Options’.
- Select option Repeat every: 1 hour until cancelled. But you can choose any other time interval for your scheduled export.
- And we will set Custom File name to – ‘ExcelifyProductsExport’
- Uncheck – Zip CSV files.
- Chose an export format – ‘Excelify: CSV‘
- Press ‘Export’
3. Copy exported file link and make public downloads available
You can copy the exported files link by right-clicking ‘Download Exported File’ button and selecting the option ‘Copy link address’.
Then head into Excelify settings in your store and in section “Security” select the checkbox Allow downloading your files by external services.
This is needed so that file can be downloaded from the URL using the link by other services than the user or the store where the file was created.
4. Create a new Google Sheets sheet
Head over to https://docs.google.com/spreadsheets/ and select ‘Blank’ sheet
5. Use a formula to automatically get data from the exported file
In cell A1 use following formula =IMPORTDATA(). In this formula, you need to paste the link you copied from ‘Download Export File’ button, so it looks something like this –
=IMPORTDATA("https://app.excelify.io/files/excelify-renars/12349b7a4c12c12323a12345adbc5e8d/export/ExcelifyProductsExport.csv")
Success!
Now you have the newest data always at hand and always in the same place. It’s worth noting that Google Sheets will only automatically export IMPORTDATA function every hour so, there is no reason to set your scheduled exports too often.
Good to know!
- Afterwards, you can construct another sheet named ‘Products’ and reference information from this imported sheet to construct a file to import back into Excelify from Google Sheet. More info about how to import from Google Sheets.
- A maximum of 50 IMPORTDATA calls are supported on a single spreadsheet.
- If you have been struck with the IMPORTDATA size limit, then these steps can help you to get around it:
- Set up multiple CSV exports with the same checkboxes, but apply a filter for each, in an example, export the Products whos price is less than 100 and Products whos price is greater or equal to 100.
- Append two IMPORTDATA functions with an array.
- You can check your scheduled export jobs when you click on ‘History & Schedule’ in the Export window.
- Google Sheets will refresh =importdata function only every 1 hour. https://support.google.com/docs/answer/58515?hl=en
- If you run into any issues or have some questions feel free to contact us – https://excelify.io/contact-us/
- Read more about what you can achieve in Excelify How to Export Shopify Products tutorial.
- Read Excelify tutorial on how to export data from Shopify with custom columns.
