Google Sheets Integration
Google Sheets Scripting API is a convenient way to create ad hoc integrations with various online services and APIS. Sheets also have powerful analytical and visualization capabilities.
Please open the following worksheet and choose Make a copy from the file menu:
https://docs.google.com/spreadsheets/d/18eK3oL2T0d9DpsjLIe3Xsifw76Q1_XVn9TnnCwDns9Y/edit?usp=sharing
Replace host with your host name and token with your token
token your token
Remaining sheets reference the above values
On sheets
=FETCHDATA(... Cell with gray background contains formula
Column A on query sheets contains query string, and parts it has been constructed of
Query parts in BLUE text represent significant variables for given layout
Open the Script editor (Tools menu) to examine the code
You can customize the code as needed
For most cases the existing code should be working as expected and you only need to modify the query string
If you have errors
#ERROR in the data formula cell indicates that API might have returned an error message. Tooltip should contain further information about the error.
#REF in the data formula cell indicates you may need to review script execution permissions. Refer to Google Sheets documentation.
TIPS:
- on PIVOT sheet try removing "category" from rows (in the query string area cell A7)
- on SALES sheet try substituting >0 (condition cell A26) with >500