x
Square Champion

Export your item urls, inventory and image urls + other data to google sheets automatically!

file-40BwTkQ4wDADNzAXFTHgJFkn.png

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

  1. 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.

  2. 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.

  3. 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.

  4. 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.
  5. 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 🙂 

531 Views
Message 1 of 10
Report
1 Best Answer
Square Champion

Best Answer

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

View Best Answer >

529 Views
Message 2 of 10
Report
9 REPLIES 9
Square Champion

Best Answer

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

530 Views
Message 2 of 10
Report
Admin

woah, thanks for this @JTPets!!

Max Pete
Community Engagement Program Manager, Square
Square Community
516 Views
Message 3 of 10
Report
Square Champion

I don't know how to tag the correct Brian from the other forum, but he helped me get a couple of last pieces of data that for me made this sheet useable vs fun to play with! 

514 Views
Message 4 of 10
Report
Square Champion

377 Views
Message 5 of 10
Report
Square Champion

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

  1. 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.
  2. 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....

 

Screenshot 2024-11-02 152037.png

351 Views
Message 6 of 10
Report
Square Champion

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:
Screenshot 2024-11-04 123900.png

@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)

 

267 Views
Message 7 of 10
Report
Square Champion

177 Views
Message 8 of 10
Report

Awesome job! Thanks for sharing this. 

I always love a good set of API code! 

 

I might need to collaborate with you on a little bit of API code I've been working on that I can't seem to figure out how to get what I'm trying to do. Usually the square API is pretty robust and gives me what I want. I'm not really sure why I'm stuck on this one. I might reach out to you. 😊

~Cheryl!

Square Champion

Sign in and click Mark as Best Answer if my reply answers your question!


Cheryl! Tisland
Burst Of Butterflies Create & Paint Studio

BurstOfButterflies.com
174 Views
Message 9 of 10
Report
Square Champion

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

173 Views
Message 10 of 10
Report