All Collections
Import Scripts
Writing an Import Script connection
Writing an Import Script connection

How to structure your Javascript and Python code

J
Written by Jamie Osler
Updated over a week ago

Import Scripts can be used to build custom integrations with Equals; pulling in data from outside sources such as third party API's. This article will cover how to write an import script. To learn more about how to set up the script in Equals and pull data, see here.

We currently have two different languages that can be used to build Import Scripts: Javascript (Node 16) and Python (3.9) - you can swap between these for each connected sheet.

Javascript

Usage

First, import the equals library via a require statement

const equals = require("equals");. Then add data and set headings with the below methods.

equals.addRows(data)

Add rows of data to the sheet. Data must be an array of arrays, or an array of objects. Can be called repeatedly to add more data. If you set headers (below) and call addRows with an array of objects, we will use the header values to pluck corresponding values from the objects.

equals.addRow(data)

Add a single row of data (a list or an object).

equals.addHeaders(headers)

Add headings to the data being imported into the sheet. Should be an array of string objects. If not called, then either the keys of the first object, or the values of the first array will be used as headings.

equals.getSecret(secretKeyName)

Retrieve a secret passed to your script from your defined secret groups.

Python

Usage

First, import the equals library with import equals. And then use the following methods to add data to your sheet.

equals.add_rows(data)

Add rows of data to the sheet. Data must be a list of lists, or a list of dicts; such as from JSON. Can be called repeatedly to add more data. If you set headers (below) and call addRows with a list of dicts, we will use the header values to pluck corresponding values from the dicts.

equals.add_row(data)

Convenience method for a single row of data (a list or a dict).

equals.add_headers(headers)

Add headings to the data being imported into the sheet. Should be a list of string objects. If not called, then either the keys of the first dict, or the values of the first list will be used as headings.

equals.get_secret(secret_key_name)

Retrieve a secret passed to your script from your defined secret groups.

Example - retrieving lead data from Intercom with Javascript

We'd like to get a list of all our leads in Intercom. We can do this with some custom Javascript. After adding a secret group with the API key we need, we can use the secret in the script:

const equals = require("equals");
const axios = require("axios");
const luxon = require("luxon");

const apiKey = equals.getSecret("intercom_api_key");
const role = "lead";

function unixToDateTime(unixTimestamp) {
return luxon.DateTime.fromSeconds(unixTimestamp).toFormat('yyyy-LL-dd HH:mm:ss');
}

let response = await axios.post("https://api.intercom.io/contacts/search",
{
query: {
field: "role",
operator: "=",
value: role
},
pagination: { per_page: 150 },
sort: { field: "created_at", order: "descending" }
},
{
headers: {
Authorization: `Bearer ${apiKey}`,
Accept: "application/json"
}
});

equals.addHeaders(["name", "email", "created_at"]);
response.data["data"].forEach((row) => {
row["created_at"] = unixToDateTime(row["created_at"]);
equals.addRow(row);
});

Example - fetching stock price with Python

We'd like to be able to pull in stock price information for AAPL on a monthly basis. To do this we can fetch the information from a remote API, and then get it automatically inserted into the spreadsheet.

import equals
import requests
import os


api_key = equals.get_secret("alphavantage_api_key")
query = f"https://www.alphavantage.co/query?function=TIME_SERIES_MONTHLY&symbol=AAPL&outputsize=compact&apikey={api_key}"

r = requests.get(query)
data = r.json()

equals.add_headers(["End of month", "Open", "Close"])

for eom, datapoint in data["Monthly Time Series"].items():
equals.add_row([eom, datapoint["1. open"], datapoint["4. close"]])

This is enough to get us up and running with Python. Here is the result, with a calculated column giving us the change month by month:

Dependencies

We provide some common dependencies for usage in Import Scripts.

Javascript Version

Node 16.

Javascript Dependencies

The following dependencies are available for usage in Javascript:

axios

0.27.2

lodash

4.17.21

luxon

3.0.1

mongodb

4.8.1

Python Version

Python 3.9.

Python Dependencies

The following dependencies are available for usage in Python:

python-dateutil

2.8.2

mixpanel

4.10.0

requests

2.26.0

flatdict

4.0.1

pymongo

4.2.0

stripe

4.0.2

Did this answer your question?