If your automatic refresh sends an error notification saying some queries failed, but the SupermetricsQueries tab in your spreadsheet shows all queries as successful, the failures may have happened because of Google Sheets formula recalculation.
When Supermetrics writes data to a spreadsheet, Google Sheets recalculates all formulas linked to the updated cells. If your spreadsheet contains many complex or chained formulas, this recalculation can take long enough to exceed Google Apps Script's 6-minute execution limit. When that limit is hit, the script exits with an APPS_SCRIPT_ERROR, and Supermetrics marks those queries as failed, even if the data was eventually written to the sheet.
To prevent this, keep your raw Supermetrics data and your formulas in separate spreadsheet files, and use the IMPORTRANGE() function to connect them. Learn more about the IMPORTRANGE function in Google Sheets help.
Before you begin
Before you reorganize your spreadsheets, map out which sheets belong to which category:
Raw data sheets: Sheets where Supermetrics writes query results. These must not contain formulas that reference the updated data.
Calculation sheets: Sheets that contain formulas and reports based on the raw data. These should be moved to a separate spreadsheet file.
You also need edit access to all spreadsheet files involved.
Instructions
Create a new spreadsheet for your formulas and calculations. This will be your calculations-only file, and it won't be touched by Supermetrics.
Set up data mirroring with
IMPORTRANGE().For each raw data sheet in your Supermetrics spreadsheet, create a corresponding sheet in your new calculations file and use
IMPORTRANGE()to pull the data across:=IMPORTRANGE("URL_OF_YOUR_SUPERMETRICS_SPREADSHEET", "SheetName!A1:Z")Replace
URL_OF_YOUR_SUPERMETRICS_SPREADSHEETwith the full URL of the spreadsheet where Supermetrics writes data, andSheetName!A1:Zwith the correct sheet name and range.When you first use
IMPORTRANGE(), Google Sheets asks you to allow access between the two files. Click Allow access when prompted.
Copy your formula sheets from your original Supermetrics spreadsheet into the new calculations file.
Update the formulas in the calculations file so they reference the imported data sheets within that file, not the original raw data spreadsheet directly.
Remove all formulas from the original Supermetrics spreadsheet. The only content in that file should be the raw data that Supermetrics writes and updates.
Verify the setup by running a manual refresh in Supermetrics and confirming that the data updates without errors.
How this works
After the reorganization, you should have at least 2 separate spreadsheet files:
Supermetrics data file: Supermetrics writes data here on a schedule or when you refresh manually. This file contains only raw data. No formulas reference the data being updated.
Calculations file: This is where you use formulas and build reports. It pulls data from the Supermetrics data file with the
IMPORTRANGE()function. Because it's a separate file, it doesn't slow down the script when data is being written.
The key principle is that when Supermetrics writes data to the source spreadsheet, Google Sheets doesn't need to recalculate anything in other files at that moment. The calculations file updates asynchronously, on its own schedule, which keeps the Supermetrics script running as fast as possible.
Troubleshooting
If queries continue to fail after you've reorganized your spreadsheets, check for any remaining formulas in the Supermetrics data file that reference the updated cells. Even a single chain of dependent formulas can cause the same timeout issue.
If you're not sure which formulas are causing the problem, contact our support team and include the name and URL of the spreadsheet. Our support agent can check the logs to identify which queries are timing out and why.
If you have any questions, contact our support team.