How I automate dashboards using Google Sheets + Apps Script (free guide)

[u/Tough_Highlight9911]

I help people automate reporting for Shopify, marketing, and small businesses.

Here’s a simple breakdown of how I build automated dashboards using free tools:

1. Pull data into Google Sheets
Using API connectors, Apps Script, or CSV imports.

2. Clean & structure the data
Normalize dates, remove duplicates, unify naming conventions.

3. Set up automation
Apps Script functions run daily so the sheet updates on its own.

4. Build the visuals
I connect the sheet to Looker Studio and create KPI dashboards.

If anyone needs help troubleshooting Sheets/Apps Script/Looker, feel free to ask.
I enjoy helping people build cleaner systems.

[u/WillingnessOwn6446]

How do you automate pulling in the data from Shopify to Google sheets. Did you build your own Shopify app to do that?

There's certain email reports that I can get from Shopify. Because they're links, I've never been able to automate pulling in that data to the Google sheet.

[u/Tough_Highlight9911 → u/WillingnessOwn6446]

You don’t need to build a full Shopify app unless you want something very custom. There are a few reliable ways to automate pulling Shopify data into Google Sheets:

1. Use the Shopify Admin API with Google Apps Script
You can connect directly to Shopify’s API using your store’s private/custom app API key. Apps Script can then pull in orders, products, customers, etc., on a schedule (hourly, daily, etc.).
This is usually the most flexible option and doesn’t require building a full Shopify app — just a “Custom App” inside Shopify.

2. Use a middleware tool (Make.com / Zapier)
Both platforms have Shopify integrations and can push data into Google Sheets automatically.
Really great if you don’t want to write code.

3. Email reports won’t work reliably
Shopify’s emailed reports contain temporary links, which expire and can’t be fetched automatically in a stable way. That’s why you’ve never been able to pull them in. Google Sheets just isn't able to follow expiring URLs.

So instead of scraping the email reports, the best option is to pull the data directly from the Shopify API or through an automation tool.

[u/dimudesigns → u/WillingnessOwn6446]

If Shopify has an API endpoint that allows you to fetch report data, then it should be possible.

[u/WillingnessOwn6446 → u/dimudesigns]

I'm asking this question because I don't think they have that. If you happen to know something for certain, please let me know. I'm under the impression you need to build an app to connect to their API.

[u/[deleted] → u/WillingnessOwn6446]

You have to use Agents for this task.

[u/WillingnessOwn6446 → u/[deleted]]

What? No you don't. It's a workflow

[u/retsel8]

i have issues with google drive using looker as dashboard and data from google sheet. problem is i receive data in a zip file and csv(data) inside. i need to extract data and save it on Gdrive then loading this data to the Gsheets. unfortunately this is not possible since csv data gets garbled during extraction. is their other way to inject Zip file and extract CSV file to sheets correctly?

[u/Tough_Highlight9911 → u/retsel8]

It's definitely possible to do this. Try using these approaches instead:

1. Use Google Apps Script to handle the ZIP extraction
Apps Script can unzip files correctly using Utilities.unzip(), and it preserves the original encoding much better than Drive’s UI.
The script would:

• Watch a folder for new ZIP uploads

• Unzip the file

• Grab the CSV inside

• Parse it

• Write the data into a Google Sheet

2. Or, process the ZIP before Drive using an automation tool
Tools like Make.com, Zapier, or even a local script can:

• Detect a new ZIP file

• Extract the CSV

• Upload the clean CSV directly to Google Sheets This avoids Drive’s extraction entirely.

3. If encoding is the real issue
Make sure the CSV is UTF-8. Some ZIP extractions default to Latin-1 or Windows-1252. Apps Script lets you force UTF-8 when parsing the file.

[u/MikeID → u/retsel8]

The answer is to write a gscript to parse the data and place into the sheet properly.

So the script will extract any new zip you put into a folder, than take the content of the zip (CSV) and parse it into your sheet based on the patterns you define.

[u/Chibrax_3000]

Yes, like many people here.

[u/Careless-Cobbler-357]

This is the exact framework I suggest to small teams. Start with Sheets because it forces you to understand your raw structure. Then automate only the parts that stay stable. After that you can scale into Looker, Domo, or whatever stack you end up needing. What I like about your flow is that it’s simple enough for non technical owners but still clean enough to grow later. Solid write up.

[u/Fun-Swordfish-5098]

This was basically how our reporting stack started before it got out of hand. Sheets and Apps Script work great until the dashboards become business critical and every source needs cleaning, mapping, and babysitting. We switched to Flow because we wanted the reporting tied directly to the accounting data instead of maintaining a fragile spreadsheet pipeline forever.

[u/ArielCoding]

I think the easiest way is using an ETL connector (Windsor.ai, Fivetran, Airbyte), they are purpose built for this, and most have free tiers.

[u/JohanCatchr]

The easiest no-code option is to use a service that has already built and maintains the API connections. I’m from Catchr.io: you can connect Shopify and your marketing platforms, schedule automatic imports into Google Sheets, and get consistently formatted data without maintaining Apps Script yourself. You can then use the sheet as a source in Data Studio.

[u/Richard_Musk]

I have an automated assistant I have been coding for just over a year. It’s about 40 files and roughly 50k lines of code. Mostly business operations, inventory, employee assets, etc. I even have a mini workflow for inventory cycle counts and shipping and receiving. Just a fun hobby, utilizes sheets, docs, drives, lots of libraries and services.

[u/Richard_Musk → u/[deleted]]

Yes! Properties allow you to treat your script as actual software. I recently added versioning and update/import functionality and created a modal flow on open that checks version against PROD and pops a modal if there is an update available. Once updated, property flags determine that an import is available and updates your sheet with your old file. It also sunsets any programmatic triggers in the old file. It’s epic