Hey everyone,
I've been on a journey for the past five years to automate my business processes but, like many of us, lack the coding expertise (and budget for hiring a developer) to get it all done. That's where ChatGPT came in—helping me create a Google Apps Script that automates specific catalog data exports from Square right into Google Sheets. Now, I’m sharing what I’ve learned and built with the hope that others can benefit and maybe even join in to improve this tool further!
About This Project
This project is essentially a Google Sheet with built-in Apps Script that connects directly to Square’s API. All you need is a Square API key from your developer dashboard. The script will pull catalog data into Google Sheets and organize it across columns. It’s something I actively use in my own business, and while it’s still a work-in-progress, it’s saved me hours of manual data handling already.
Getting Started
Make a Copy of the Sheet: Start by making a copy of the Google Sheet provided. From there, you’ll need to insert your API key to enable Square API access.
Understand the Basics of API Keys: This script requires an API key from your Square Developer account. Keep in mind that API keys are powerful and must be protected. If used improperly, they can access a lot of data, so ensure you understand the basics of API security.
Using the Script: The script will pull and populate the following data fields automatically:
- Item ID
- Item Name
- Variation ID
- Price
- Stock Availability by Location
- Image URLs
- Category, Modifiers, and Custom Attributes
- And many more…
Some fields might not be fully functional yet (like advanced custom attributes), but I’m not too focused on those at the moment. Feel free to adjust or contribute if any fields are a priority for you.
How to Use the Square API Menu:
- Set API Key: Click on Square API > Set API Key to enter your API token.
- Set Email Address: This allows the script to send notifications when a data refresh completes successfully or encounters errors.
- Start Processing: This pulls and refreshes catalog data whenever needed.
- Set Daily Timer: This sets the script to refresh data daily at a specified time, automating the entire process.
Proceed with Caution: While I’m sharing this tool, please use it responsibly. I’m not responsible if something breaks or if API keys are mishandled. Always test with dummy data if you’re unsure.
Opening Up for Collaboration
This tool is far from perfect and still a bit rough around the edges, so I welcome anyone interested in refining it. I believe that sharing tools like this can empower others to grow their business acumen without needing to rely on expensive development resources.
I’ll paste the code below for review, but please note that I’m not here to troubleshoot individual issues. This is a community project, and I encourage anyone willing to share improvements, additional functionality, or even just feedback.
Happy automating, and looking forward to seeing how others might enhance this!
Thanks to @bryan-square on the developer forums for the assists that got this across the finish line for me to a level where I can being to automate ordering and other regular activities with a headless script that pulls all the details I need to "get started" hopefully I can start to get vendor information next 🙂
V1.0:
Can't paste code here due to length so here is a pastebin: https://pastebin.com/hVncux2e
V1.1:
Logic corrected in is active to account for items that are "active at all future locations" but inactive at some locations: https://pastebin.com/CKN2WAVA
V1.0:
Can't paste code here due to length so here is a pastebin: https://pastebin.com/hVncux2e
V1.1:
Logic corrected in is active to account for items that are "active at all future locations" but inactive at some locations: https://pastebin.com/CKN2WAVA
V1.1:
Logic corrected in is active to account for items that are "active at all future locations" but inactive at some locations: https://pastebin.com/CKN2WAVA
Now that you've exported the data using an API call, you might be wondering, 'What’s next?' To keep the data safe from accidental deletions or edits, we’ll move it to a separate document. I've created a new document and set up an Apps Script within it to automatically pull the data into a fresh sheet, formatting it for easy sorting and use.
Purpose
This script automatically copies data from a "source" Google Sheet to a "target" Google Sheet when the target sheet is opened. It also adds a menu option called "Custom Tools" with a "Refresh Data" button to manually trigger data updates.
Instructions for Use
Update the Source and Target Sheet IDs:
- Find the Sheet ID by opening your Google Sheet. The Sheet ID is the random mix of letters and numbers in the URL between /d/ and /edit.
- Replace the current Source Sheet ID (starts with 1RYj8gN9...) with the ID of the sheet you want to copy data from.
- Replace the current Target Sheet ID (starts with 1rrYfiZl...) with the ID of the sheet you want to copy data into.
Sheet Names:
- Ensure the sheet names ("API-Export" in the source and "AutoCatalogUpdate" in the target) match the names of the specific tabs in each Google Sheet.
How It Works
- When the target Google Sheet is opened, this script:
- Clears all data in the target sheet and replaces it with data from the source sheet.
- Freezes the header row.
- Sorts the data by Column C, excluding the header.
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Tools')
.addItem('Refresh Data', 'copyData')
.addToUi();
// Automatically run copyData when the sheet is opened
copyData();
}
function copyData() {
// Open the source and target spreadsheets
const sourceSpreadsheet = SpreadsheetApp.openById("SOURCE");
const sourceSheet = sourceSpreadsheet.getSheetByName("API-Export");
const targetSpreadsheet = SpreadsheetApp.openById("DESTINATION");
const targetSheet = targetSpreadsheet.getSheetByName("AutoCatalogUpdate");
// Get the range of data in the source sheet
const sourceRange = sourceSheet.getDataRange();
const sourceValues = sourceRange.getValues();
// Clear the target sheet and paste the values
targetSheet.clearContents();
targetSheet.getRange(1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);
// Freeze the header row
targetSheet.setFrozenRows(1);
// Sort the data by Column C (3rd column) alphabetically, excluding the header
targetSheet.getRange(2, 1, sourceValues.length - 1, sourceValues[0].length).sort({ column: 3, ascending: true });
}
Once I did that I created a ton of simple spreadsheet filters to get automatic updates to formulas for sheets like. Your filters may be different depending on the columns downloaded, but these are simple examples for you to work with:
No URL - could be hidden, deleted, or many reasons
=ARRAYFORMULA({AutoCatalogUpdate!A1:AN1; FILTER(AutoCatalogUpdate!A2:AN, ISBLANK(AutoCatalogUpdate!D2:D))})
No Tax - Items with no tax for audits (all my items should have tax)
=ARRAYFORMULA({AutoCatalogUpdate!A1:AN1; FILTER(AutoCatalogUpdate!A2:AN, ISBLANK(AutoCatalogUpdate!AC2:AC))})
No Categories - Can't sell it if they can't find it
=ARRAYFORMULA({AutoCatalogUpdate!A1:AN1; FILTER(AutoCatalogUpdate!A2:AN, ISBLANK(AutoCatalogUpdate!Y2:Y))})
Negative Inventory - Sold below 0
={ AutoCatalogUpdate!A1:AQ1; FILTER(AutoCatalogUpdate!A2:AQ, (AutoCatalogUpdate!AM2:AM < 0) + (AutoCatalogUpdate!AN2:AN < 0)) }
Others I am working with:
Transfers - for items OOS in 1 location but not another
Order calculator - combines active status, and min stocks to generate order qty
More to come as i move forward with automations....
With a few data points from a normal square catalog export I have no created an auto updating workbook that generates orders and costs automatically I just select a vendor from a drop down:
@alexschiff AI FTW! but this is a feature I would LOVE to see square automate for me as a desktop function, purchase orders based on minimum stock levels, with alerts for items that are out of stock at one or more lcoations (a feature I still need to add)
No idea what i'm doing but happy to collaborate 🙂 I keep using the ChatGPT o1-preview model and whenever I can't get the code to work I search API documentation for what im looking for and copy and paste the entire webpage into the chatbot and go "Does this help? PASTE"
It almost always fixes whatever is broken lol
