Some solutions such as Power BI, Tableau, or Excel natively understand structured data. They also offer native support for JSON Web APIs such as OData. However, Google Sheets does not offer the same level of support (data import functions are limited).
However, by adding a script extension to Google Sheet, it becomes possible to import JSON data from Web APIs into Google Sheets.
First, add an extension script to Google Sheets:
- Go to this GitHub page
- Click on the "Raw" button
- Copy the script's text from the main browser window
- Go to Google Sheets
- Click on Extensions > App Scripts
- Paste the script's text from step 3 and save
A new function called ImportJson() is now available within Google Sheets.
Next grab the Sales Cookie URL with the entity you want to access. For example, to retrieve plans:
- Login to Sales Cookie
- Go to Settings > Connections > OData API
- Select the Plan entity on the left pane
- Copy the URL under "Retrieve Top 5000 Plan Records"
You can now invoke ImportJson() from Google Sheet. It should look like this. Please note the following:
- The hidden text below should be your own (refer to step 4 above)
- The script adds "Value" to each field's name (rename fields or update the script)
- Additional scripting may be required to page through records, join between different entities, etc.
Sales Cookie did not author or contribute to the script above. Sales Cookie did not review the script above for security, performance, or privacy issues. You are responsible for reviewing this script before adding it to Google Sheet. Sales Cookie cannot take any responsibility for external resources not under our control.