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