Getting Started with Google Sheets

The Fusebit Google Sheets Add-On allows you to get external data into Google Sheets with Node.js, NPM, and Fusebit ConnectorConnector - A connector is the package from Fusebit that manages the relationship between one or more integrations and a specific service. One of the most common types of connector is an OAuth connector, which takes care of the OAuth negotiation between your customers and the service you're integrating, so that you don't have to!s.

Fusebit Google Sheets Add-OnFusebit Google Sheets Add-On

Fusebit Google Sheets Add-On

Here are examples of what you can do in your Google Sheets spreadsheet using this add-on:

  • Get the latest sales data from Salesforce
  • Access stock or digital currency prices
  • Get daily payments report from Stripe
  • Access Github or Linear issues opened in the last day
  • Connect to thousands of other APIs and data sources using Node.js, NPM, and Fusebit Connectors

Getting Started

Open the Google Sheets spreadsheet you want to add the external data to, go to Extensions | Add-ons | Get add-ons menu, search for fusebit, and install the add-on.

Installing the Fusebit Add-On for Google Sheets from the Google Workspace MarketplaceInstalling the Fusebit Add-On for Google Sheets from the Google Workspace Marketplace

Installing the Fusebit Add-On for Google Sheets from the Google Workspace Marketplace

To initialize the add-on, go to Extensions | Fusebit | Open. A sidebar will show up with more options. You will be using this sidebar whenever you want to add or modify a custom Node.js function you can call from your spreadsheet.

The Fusebit Add-On sidebarThe Fusebit Add-On sidebar

The Fusebit Add-On sidebar

Select Edit in the sidebar. A new tab will open and ask you to authenticate to Fusebit using your Google account to associate it with your Google Spreadsheet. You will then see the Fusebit Editor where you can use Node.js, NPM, and Fusebit Connectors to develop custom functions you can call from your Google Spreadsheet.

Fusebit EditorFusebit Editor

Fusebit Editor

Switch back to the Google spreadsheet and click Check again in the sidebar. The list of Fusebit functions available for use from your spreadsheet will show up. Fusebit provides a few out of the box function to get you started: covidNewCasesByState, double, and weather.

Checking available Fusebit functions in the sidebarChecking available Fusebit functions in the sidebar

Checking available Fusebit functions in the sidebar

Let's use the covidNewCasesByState function in your spreadsheet to look up the most recent COVID-19 infection numbers in a few states. Enter the state abbreviations in the A1:A4 range, and then =FUSEBIT(A1:A4,"covidNewCasesByState") rule in cell B1. Press enter and see the number of infections showing up in range B1:B4.

Using Fusebit functions in a Google SpreadsheetUsing Fusebit functions in a Google Spreadsheet

Using Fusebit functions in a Google Spreadsheet

Congratulations! You just used the Fusebit Add-On to call one of the provided Node.js functions from within a Google spreadsheet and get the latest COVID-19 infection data from an external service (covidactnow.org in this example). Read on to learn how to implement your own.

Calling Fusebit Functions

The Fusebit Add-On for Google Sheets adds a new custom function to your spreadsheet called FUSEBIT. It can be used to invoke one of the named Fusebit functions you develop in Node.js.

To call a custom Fusebit function in your spreadsheet, add a =FUSEBIT(data, "{functionName}") rule to the desired cell. It will invoke the Fusebit function named functionName and provide it with the input data, which can be a literal (string, number, boolean) or a reference to a single spreadsheet cell or a range of cells.

Calling a custom Fusebit functionCalling a custom Fusebit function

Calling a custom Fusebit function

The results of calling the custom function will be rendered either as a single value in the cell where you placed the =FUSEBIT(...) rule, or as a range of values having that cell in the upper-left corner, depending on what you decided the Fusebit function to return, as described in the next section.

Adding New Fusebit Functions

With Node.js, NPM, and Fusebit Connectors at your disposal, you can fetch external data into your spreadsheet from thousands of sources. All you need to do is add an extra function.

First, open the Fusebit Editor by going to Extensions | Fusebit | Open in your Google spreadsheet and clicking Edit in the sidebar. Then, locate the googleSheetsFunctions object in the integration.js file. Each property of this object is an async JavaScript function written in Node.js that you can call from within your Google spreadsheet.

Add a simple function called timesSeven within googleSheetsFunctions object:

const googleSheetsFunctions = {

  timesSeven: async (ctx, data) => {
    return data * 7;
  },

  //...
}

Save the code in the Fusebit Editor, switch back to your Google spreadsheet, and enter the =FUSEBIT(14, "timesSeven") rule in one of the cells. You should see 98 show up as a result, indicating your custom Fusebit function has been called.

Programming Model

Each custom Fusebit function to be exposed for calling from Google Sheets must be defined as a property of the googleSheetsFunctions object in the integration.js file:

myFunction: async (ctx, data, charts) => {},

It must be an async function and accepts three arguments:

  • ctx. This is an object representing the request from your Google spreadsheet, and you can largely ignore it except when using Fusebit ConnectorConnector - A connector is the package from Fusebit that manages the relationship between one or more integrations and a specific service. One of the most common types of connector is an OAuth connector, which takes care of the OAuth negotiation between your customers and the service you're integrating, so that you don't have to!s to connect to external services as explained in the Using Fusebit Connectors section.
  • data. This is the data specified as the first argument in the call to the =FUSEBIT(data, "{functionName}") rule in your Google spreadsheet. It can be a literal (number, string, boolean), but more often it is a reference to a single spreadsheet cell or a range of cells. If =FUSEBIT(data,...) refers to a single cell, the data argument of your Fusebit function represents the value of that cell. If a range of cells is used in the =FUSEBIT(data,...) call in the spreadsheet, the data argument of your Fusebit function is a two-dimensional array with the values of that range, with the first dimension representing spreadsheet rows and the second columns.
  • charts. This is an object mapping Google Sheet chart title to a string representing the base64-encoded PNG image of the given chart. This hash only contains the charts the spreadsheet user decided to make available to Node.js by including them as the 3rd parameter of the call to the =FUSEBIT(...) spreadsheet function. The third argument can be a single chart title (e.g. =FUSEBIT(A1,"myFunc","My Chart")), or an array or an array of chart titles (e.g. =FUSEBIT(A1,"myFunc",{"My Chart","Your Chart"})). See below for more examples or check out Sending Google Sheet Charts to Slack for a walkthrough.

The function can return a single value (string, number, boolean), or a two-dimensional range of values:

  • When a single value is returned, in will be rendered in the spreadsheet cell where the =FUSEBIT(...) rule is defined.
  • When a two-dimensional range of values is returned, it will be rendered in the range of cells of the spreadsheet where the upper left cell contains the =FUSEBIT(...) rule. For such a two-dimensional return to succeed, the range of cells in the spreadsheet must be empty.

You can implement functions that accept a single value and return a range, accept a range and return single value, or accept and return a range. Let's have a look at a few examples.

Accepting and Returning a Single Value

This is a function that accepts a string and returns its base64 encoding:

base64: async (ctx, data) => {
  // data is "abc"
  return Buffer.from(data).toString('base64');
}

This is how it is used on a single cell in the spreadsheet:

Accepting and returning single valuesAccepting and returning single values

Accepting and returning single values

Accepting and Returning a Range

This is a function that accepts a range of values representing circle radius, and returns a corresponding range with the circumference of those circles:

circumference: async (ctx, data) => {
  // data is [ [ 1, 2 ], [ 3, 4 ], [ 5, 6 ] ]
  return data.map(row => row.map(cell => 2 * 3.14 * cell));
}

And this is how it is used on a range with three rows and two columns:

Accepting and returning a range of valuesAccepting and returning a range of values

Accepting and returning a range of values

Passing Charts to Node.js

If your spreadsheet contains a chart titled Sales, you can pass that chart to the Node.js function by specifying its title as the third argument of the call to FUSEBIT. Consider this spreadsheet, which contains the chart titled Sales and specifies that title as the 3rd argument of the call to the FUSEBIT function:

Passing a Google Sheets chart as a PNG image to Node.jsPassing a Google Sheets chart as a PNG image to Node.js

Passing a Google Sheets chart as a PNG image to Node.js

This is how you can receive and process this chart in your Node.js function:

sendChart: async (ctx, data, chart) => {
  // data is [ [ "AU", 5 ], [ "EU", 4 ], [ "US", 3 ] ]
  // chart['Sales'] is a base64-encoded PNG image of the "Sales" chart
  const pngBytes = Buffer.from(chart['Sales'], 'base64');
  return `Chart size: ${pngBytes.length}`;
}

Using NPM Modules

You can use any public NPM module for Node.js in your Fusebit function to pull data into your Google spreadsheet from a variety of sources, from databases to APIs.

In the Fusebit Editor, open the package.json file and declare a new module dependency. For example, you could use the mongodb module to connect to a MongoDB database and pull data into your Google spreadsheet.

Use any NPM moduleUse any NPM module

Use any NPM module

Then use the module in your code like you would in any Node.js application, by loading it with require("mongodb").

Calling HTTP APIs

You can call into any external HTTP APIs from within the Fusebit functions. You can use any NPM module you see fit (from generic HTTP clients to dedicated SDKs for specific services). The superagent module is pre-loaded for you.

Let's have a look at how the provided weather function is implemented to see how to use a generic HTTP client to call external APIs.

weather: async (ctx, data) => {
  try {
    const weatherUrl = `https://api.weather.gov/points/${data[0][0]},${data[0][1]}`;
    let response = await Superagent.get(weatherUrl).set('User-Agent', 'fusebit-gsheets-addon');
    const forecastUrl = response.body?.properties?.forecast;
    response = await Superagent.get(forecastUrl).set('User-Agent', 'fusebit-gsheets-addon');
    const forecast = response.body?.properties?.periods?.[0]?.detailedForecast;
    return forecast;
  } catch (e) {
    throw new Error(`Error getting forecast for [ lat, long ] location ${data[0]}. Error: ${e.stack || e.message}`);
  }
}

The function is making two asynchronous HTTP calls using the superagent module to the free weather APIs provided by the US government. The first resolves the location of the weather station for the specified latitude and longitude, and the second obtains the weather forecast from that station.

Using Fusebit Connectors

A Fusebit ConnectorConnector - A connector is the package from Fusebit that manages the relationship between one or more integrations and a specific service. One of the most common types of connector is an OAuth connector, which takes care of the OAuth negotiation between your customers and the service you're integrating, so that you don't have to! is a powerful mechanism that allows you to easily call into external systems like Salesforce, Hubspot, Linear, Jira, Slack, Quickbooks, and more. Connectors allow you to authorize access to those systems once, and then import data from those systems into Google Sheets using your Fusebit functions.

You can use Connectors by adding SnippetSnippet - A small snippet of code that implements a specific operation on a a target system you want to connect to. A snippet defines one of more JavaScript functions you can call from your core integration logic to cut down on the implementation time.s to your integration.js code. The mechanism is explained in detail in the Adding Snippets section. Let's walk here through a Salesforce example.

Example: Importing Salesforce Data

In this example, we will import Salesforce contacts into your Google Sheets, and allow the import to be customized to only select attributes.

First, in your Google spreadsheet go to Extensions | Fusebit | Open, click Edit, and then select the Snippets button in the Fusebit Editor. In the dialog that shows up, type "salesforce" in the search field, select Salesforce - Run JQL Query snippet, and click Add.

Adding Salesforce - Run JQL Query snippetAdding Salesforce - Run JQL Query snippet

Adding Salesforce - Run JQL Query snippet

Notice the new sfdcRunQuery function that was added to your integration.js file. We will use this snippet shortly when adding a new Fusebit function to be called from your Google spreadsheet.

/**
 * Run Salesforce JQL query.
 * 
 * @param ctx {FusebitContext} Fusebit Context
 * @param jql {string} Salesforce JQL query
 */
async function sfdcRunQuery(ctx, jql) {
  //...
}

Before we do that, however, let's authorize access to your Salesforce instance by clicking the Run button in the Fusebit Editor. You will be asked to authenticate and authorize access to your Salesforce instance, and then return back to Fusebit Editor.

Authorizing access to SalesforceAuthorizing access to Salesforce

Authorizing access to Salesforce

Once you are back in the Fusebit Editor and access to your Salesforce instance is authorized, it is time to implement the Fusebit function that will use the sfdcRunQuery snippet to get Contacts from Salesforce, and format it before passing back to Google Sheets.

We want to customize the list of Contact attributes to import from SFDC. Let's design the Fusebit function so that it can be used from Google Sheets as follows:

Importing select Contact attributes from Salesforce to Google SheetsImporting select Contact attributes from Salesforce to Google Sheets

Importing select Contact attributes from Salesforce to Google Sheets

Notice the range A1:B1 passed as the first parameter contains the attribute names to import from Salesforce. We expect the function to return Contact records and lay them down, one record per spreadsheet row, in corresponding columns.

With that, let's switch back to the Fusebit Editor and add a new property to the googleSheetsFunctions object:

sfdcContacts: async (ctx, data) => {
  const attributes = Array.isArray(data) ? data[0] : [ data ];
  const jql = `select ${attributes.join(',')} from Contact`;
  const result = await sfdcRunQuery(ctx, jql);
  const range = result.records.map(contact => attributes.map(attribute => contact[attribute]));
  return range;
},

The sfdcContacts function is first normalizing the list of Contact attributes to fetch into the attributes array. Then it formulates the full JQL query to be passed to Salesforce. In line 4, it uses the sfdcRunQuery snippet we previously added to run that query agains the Salesforce instance that you authorized. In line 4, it transforms the format of the response to a Google Sheets range representation so that it renders correctly in the spreadsheet table. Save the Fusebit function, go back to the spreadseet, and use it as described above. Here is the result:

Salesforce Contact data imported into Google SheetsSalesforce Contact data imported into Google Sheets

Salesforce Contact data imported into Google Sheets

Using Fusebit Connectors, you can similarly fetch data from a number of external systems and import it into your Google spreadsheet.

Sharing and Security

You can share the spreadsheet with the Fusebit Add-On installed with others. The following rules apply:

  • Editing. Currently, only the person who originally set up the Fusebit Add-On in the particular spreadsheet can access the Fusebit Editor to add new functions or modify the existing ones.
  • Calling. Everybody you shared the spreadsheet with can call the Fusebit functions you made available using the =FUSEBIT(...) spreadsheet function. They can therefore also see any data the functions import into the spreadsheet. Everybody you shared the spreadsheet with can also list the available functions by going to Extensions | Fusebit | Open.

Refreshing the Data

Once the Fusebit function runs in a spreadsheet, it will not run again until the input parameters change. If you want to refresh the data when needed (e.g. to import the latest sales data from Salesforce that you know has changed), there is a trick you can use to force the re-evaluation.

You need to add a checkbox to a cell in your spreadsheet using Insert | Checkbox, and then include this cell in the range of values you pass in to =FUSEBIT(data,...). Whenever you toggle the checkbox in the spreadsheet, your function will re-evaluate. Note that you must account for this mechanism in the implementation of the function itself if it is otherwise expecting input data to be passed in by ignoring the boolean value representing the checkbox. Consider the weather function introduced previously:

Forcing the re-evaluation of a function with a checkboxForcing the re-evaluation of a function with a checkbox

Forcing the re-evaluation of a function with a checkbox

The weather function will re-run whenever you toggle the checkbox in C2, because it is included in the range passed in to =FUSEBIT(A2:C2,"weather"). At the same time, the implementation of the function ignores anything apart from the first two values representing [ lat, long ].


Did this page help you?