How Can I Import Sales Cookie Data Into Google Sheets?

How Can I Import Sales Cookie Data Into Google Sheets?


Sales Cookie offer a rich entity schema and APIs.

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:
  1. Go to this GitHub page
  2. Click on the "Raw" button
  3. Copy the script's text from the main browser window
  4. Go to Google Sheets
  5. Click on Extensions > App Scripts
  6. 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:
  1. Login to Sales Cookie
  2. Go to Settings > Connections > OData API
  3. Select the Plan entity on the left pane
  4. 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.



Security Note
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.

    • Related Articles

    • Getting Started With Sales Cookie - Admins

      About This Guide This guide is designed to help you get started as a Sales Cookie administrator. We assume that we've already configured one or more incentive plans for you. Looking for a starter guide for your payees instead? Click here. This guide ...

    • How Secure Is Sales Cookie?

      Many SaaS products claim they are secure simply because they use SSL. The reality is that a SaaS solution requires many measures to be truly secure. Here is an overview of advanced security measures we implement to deliver world-class security. Some ...

    • How Does Sales Cookie Scale Calculations To Very Large Data Sets?

      Behind the scenes, Sales Cookie is able to scale calculation of commissions over large numbers (millions) of transactions using the following: Elastic Cloud Compute Parallel Data Processing Code Optimizations Elastic Cloud Compute The Microsoft Azure ...

    • Getting Started With Sales Cookie - Payees

      About This Guide This guide is designed to help you get started with Sales Cookie as a payee. For the admin starter guide, click here. This guide does NOT cover the many advanced options or capabilities found within Sales Cookie. However, we will ...

    • How Can I Embed Web Content Within Sales Cookie?

      You can create and publish announcements to personal incentive dashboards. However, sometimes you already have some existing content you want to show within Sales Cookie. This could be: Sales resources Sales training guides Various documents (ex: ...