There are a lot of patterns for interacting with Google Sheets. This page organizes these to help you find what you need.
Pulling data into Google Sheets
- From Public Looks
- Using ImportXML
- Two implementations of custom functions to replace ImportXML: 1, 2
- From API endpoints
Pulling from a CSV endpoint (for query results)
- Pulling from a JSON endpoint
I would highly recommend using webhooks sent to Zapier to automate spreadsheets.
I’m in charge of updating multiple reports on a weekly basis that took hours of manually updating before I started using Zapier.
After this amazing find, I’ve been able to focus on meaningful work only and no longer wasting hours on updating spreadsheets.
After Zapier, I would say creating the menus in Sheets that you can use to refresh data is the next best/reliable way to go from my personal experience.
Pros: This won’t crash your data like other imports will, the data will stay there for however long you want it there for. Also the loading time is pretty quick
Con: If you’re pulling a larger amount of data the script will have a hard time pulling the data over to google sheets. (This also creates one tab per look, it won’t allow you to put multiple looks into one tab)
Hack for bigger looks: When pulling larger amounts of data through the script you can open up the looks you’re using to integrate into the spreadsheet and let them load. Once loaded go to the menu your script made and update your data. The reason this works is because it creates a cache that the google script can pull from taking the majority of the load off of google scripts.
If anyone needs help getting either of these to work I’m more than happy help!