Get your Google Sheets data ready for a Power BI dashboard

Short answer: Use a free Google Apps Script that combines your monthly or per-location tabs into one clean table, plus a calendar table, ready for Power BI to read. It runs in your own Google account; nothing is sent to us. Setup takes 6 steps: Use the same headers; Make a Settings tab; Paste the script; Build the tables; Publish them as CSV; Connect Power BI.

How to set it up

1. Use the same headers. Each tab you want in the dashboard needs the same column names in row 1, such as Date, Customer, Amount.
2. Make a Settings tab. Add a tab called Settings. In B1, list the tabs with commas (Jan,Feb,Mar), or type the start of their names (such as Sales ) to take every tab that starts with it.
3. Paste the script. Open Extensions, then Apps Script, paste in the code from our email, and save. Reload the sheet and a Power BI menu appears.
4. Build the tables. Choose Power BI, then Rebuild the tables now, or Rebuild every hour. You get PowerBI_Data, with every row and a Source column naming its tab, and PowerBI_Dates, a calendar table with year, month, quarter and weekday for each day.
5. Publish them as CSV. In the sheet, choose File, Share, Publish to web. Pick PowerBI_Data and Comma separated values, publish, and copy the link. Do the same for PowerBI_Dates.
6. Connect Power BI. In Power BI Desktop, choose Get data, Web, paste the first link and load it. Repeat for the dates link, then relate the two tables on Date and build your visuals.

Want this set up for your business? Free written scope in 20 minutes

Get the script

Good to know

• Publish to web makes those two tabs readable by anyone who has the link. Keep private data out of them.
• Dates are written as 2026-11-07 so Power BI reads them as dates in any region.
• If one tab's headers differ from the first, the rebuild stops and names the tab, so a dashboard never mixes up columns.

Questions

What does this script do?

It is a free Google Apps Script that combines your monthly or per-location tabs into one clean table, plus a calendar table, ready for Power BI to read.

How do I set it up?

In 6 steps: 1. Use the same headers; 2. Make a Settings tab; 3. Paste the script; 4. Build the tables; 5. Publish them as CSV; 6. Connect Power BI. The full steps are on this page.

Is it free, and does it send my data anywhere?

It is free: enter your email on this page and we email you the script (Code.gs). It runs in your own Google account; nothing is sent to us.

Can you install it for me?

Yes. We set it up in your Google account in a short Connect session, connect it to your sheet and check that it runs.

Want this connected to the rest of your tools? Send us a short brief.