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

host https://your.host.name

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