Guides
Go to Blog

Integrating with Google Sheets

Suggest Edits

The example here shows how you can pull in BIN data to Google Sheets for the purpose of conducting a deeper analysis of your payment transactions or customer records. This example leverages the great work that Brad Jasper has done with the ImportJSON App Script code library. Take a look at the ImportJSON README.md on Github to find out more about the library.

📘

Heads Up!

You won’t really have to write much code for this example, but there is a bit of cutting and pasting.

Before You Begin

Before you can integrate Parrot with Google Sheets, you'll need the following:

Step 1: Set up a Google Sheet and Apps Script Project

To start, you'll need to create both a new Google Sheet and Google Apps Script project:

  1. Create a new Google Sheet. Check out Google’s tutorial if you need help.
  2. Title your new Google Sheet (e.g. Parrot Pagos BINs)
  3. Click Extensions, then click Apps Script to open a new Apps Script project.
  4. Name your project (e.g. Parrot Service)
  5. Next to Files, click +, then select Script from the drop-down menu. This will create a new Apps Script file, titled Untitled.gs. We recommend renaming this file ImportJSON before hitting enter.

Step 2: Add ImportJSON

Next, add the ImportJSON library to your new Google Apps Script project. With other languages and integrations you probably use git to pull code down to your machine to work with a library; since this is basically scripting, however, you'll “install” the library using cut and paste:

  1. In the tab where you have the ImportJSON.gs Githup repository open, click Raw to access the raw code. To view this code directly, click here.
  2. Use the keystroke Command – A on Mac (or Ctrl-A Windows) to select all, then copy all the text (Command – C on Mac, Ctrl-C on Windows). This will put the code into your clipboard.
  3. Return to your Google Apps Script project. Delete all the content of your ImportJSON.js file, then paste all the text from your clipboard (Command – V on Mac, Ctrl-V on Windows).
  4. Click the save icon to save your project.

Step 3: Add a Parrot Helper Function

Rather than make you learn the syntax of the ImportJSON library, we've create a wrapper function that makes it easy to drop in your API key and get started with Parrot.

To add the Parrot helper function:

  1. In your Google Apps Script project, click Code.gs, then select all of the existing text and hit Delete.
  2. Copy in the below text and paste it into Code.gs, then save your project.
/**
 * Imports a Pagos Parrot JSON feed and returns the results to be inserted into a Google Spreadsheet. Uses the ImportJSON library.
 * 
 * By default, data gets transformed so it looks more like a normal data import. Specifically:
 * 
 * @param {url}          the URL to a public JSON feed
 * @param {query}        a comma-separated list of paths to import. Any path starting with one of these paths gets imported.
 * @param {parseOptions} a comma-separated list of options that alter processing of the data
 * @param {key}          a Pagos Parrot API Key
 * @customfunction
 *
 * @return a two-dimensional array containing the data, with the first row containing headers
 **/
function importParrotBINJSON(url, query, parseOptions, key) {
    var apikeyheader = {headers: {"x-api-key": key}};

    return ImportJSONAdvanced(url, apikeyheader, query, parseOptions, includeXPath_, defaultTransform_);
}

Your project should look like this now.

1024

Step 4: Make Your First Parrot BIN Query

You can now close the Apps Script editor window or tab. Before we make a call, ensure you have your API Key and the Pagos URL.

To make your first Parrot BIN query:

  1. Go to cell A1 in your Google Sheet and type in an example BIN, such as 535316.
  2. Go to cell B1 and type in the function to call Parrot – note you need to replace the blue text (including brackets) with your API key:
=importParrotBINJSON("https://parrotv2.prod.pagosapi.com/cards?bin="&A1, ,"Headers", "[your API key]")

That’s it! You’ve called Parrot from within Google Sheets. Your Google Sheet should look like this now:

1024

Analyze More Data

After you complete each of the steps above, you can begin pulling down card details, creating pivot tables, and analyzing data to your heart's content!

The ImportJSON library allows you to customize the output of the importParrotBINJSON call using the same options as ImportJSON. You can turn off headers and even customize the data you want. To get you started, we've pasted an additional example that removes the column headers (the noHeaders setting) and only returns the Network, Card Type, Card Product and Card Country:

=importParrotBINJSON("https://parrotv2.prod.pagosapi.com/cards?bin="&A1, "/card/product/product_name,/card/network,/card/type,/card/country/alpha2,/card/bank/name","noHeaders", "[your API key]")

You can see what this query result looks like—just the selected data and no headers.

770

Queries and Quotas

It’s really easy to make a lot of requests to the Parrot service when working in Google Sheets, so it’s important to consider both how much data you are working with manually this way, and Google's limits on UrlFetch. Calling our service from Google Sheets will count against your quota for Google Services. If you are on a managed Google account, you may need to contact your Account Admin to understand more about your quotas.

Updated over 2 years ago

What’s Next