Edit on val.town ↗

Store data in a Google Sheet

You can send data to Google Sheets from a val. You can collect data from incoming HTTP requests or from an HTTP endpoint on a schedule.

You have two options for authenticating with the Google Sheets API:

  1. Use Pipedream's Accounts API to fetch a fresh OAuth access token at runtime.
  2. Use your own Google Cloud service account.

Pipedream provides a workflow automation product for developers. Workflows are built as a sequence of steps — pre-built actions for thousands of APIs or custom code — triggered by an event (HTTP request, timer, when a new row is added to a Google Sheets, and more).

Pipedream also manages the OAuth token refresh process for apps like Google Sheets, exposing a fresh access token in the workflow runtime and the Accounts API. If you connect your Google Sheets account to Pipedream, you can use the Accounts API to fetch a fresh token within your val and use that token to authenticate requests to the Google Sheets API.

Sign up for Pipedream and visit https://pipedream.com/settings/user. Find the API Key section and copy your key:

pipedream-api-key.png

You'll use this key to authenticate requests to the Accounts API, letting you fetch Google Sheets access tokens in your val.

Add the API key as an environment variable in Val Town. In this example, we've named the environment variable pipedream_api_key:

pipedream-val-town-env-var.png

Visit https://pipedream.com/accounts. Click the Connect an app button at the top-right:

pipedream-connect-app.png

You'll be directed through the OAuth authorization process to connect your Google Sheets account to Pipedream. Once you've connected your account, you'll see it listed on the Accounts page.

Click the … to the right of the account and Copy Account ID:

pipedream-copy-account-id.png

All account IDs are prefixed with apn_, so yours should look something like apn_abc123.

In the Google Sheet you'd like to access, copy the sheet ID from the URL bar. It's the long string of characters between /d/ and /edit.

sheet_id.png

In your val, fetch a fresh Google Sheets access token from Pipedream and use it to authenticate requests:

import { fetchJSON } from "https://esm.town/v/stevekrouse/fetchJSON?v=41";

const accountID = "<YOUR ACCOUNT ID HERE>";
const sheetID = "<YOUR SHEET ID HERE>";
const baseURL =
  `https://sheets.googleapis.com/v4/spreadsheets/${sheetID}/values`;

async function fetchAccessToken() {
  const response = await fetch(
    `https://api.pipedream.com/v1/accounts/${accountID}?include_credentials=1`,
    {
      headers: {
        Authorization: `Bearer ${Deno.env.get("pipedream_api_key")}`,
      },
    },
  );

  if (!response.ok) {
    throw new Error(`Error fetching access token: ${response.statusText}`);
  }

  const { data } = await response.json();
  return data.credentials.oauth_access_token;
}

async function makeSheetsRequest(url, method, body = null) {
  const accessToken = await fetchAccessToken();
  const options: RequestInit = {
    method,
    headers: {
      Authorization: `Bearer ${accessToken}`,
    },
  };

  if (body) {
    options.body = JSON.stringify(body);
  }

  return fetchJSON(url, options);
}

// Write data to a sheet
await makeSheetsRequest(
  `${baseURL}/A1:C1:append?valueInputOption=RAW`,
  "POST",
  { values: [[Date(), Math.random(), 1]] },
);

// Read data from a sheet
const data = await makeSheetsRequest(
  `${baseURL}/A1:C1?majorDimension=ROWS`,
  "GET",
);

console.log(data.values[0]);

Authenticating with the Google Sheets API is a bit tricky, but we walk you through it below. It should only take a few minutes, and only needs to be done once.

Google recommends OAuth2 server to server authentication for bot-based applications. We will walk you through creating a Google Cloud service account, giving it access to one of your Google Sheets, and using its key to make authenticated requests to the Google Sheets API.

Creating a Google Cloud service account is the recommended way to authenticate to the Google Sheets API.

Open the Google Cloud console and click on the Select a project dropdown in the top left.

select_project_dropdown.png

In the dialog that pops up, click on the New project button.

new_project.png

Choose any name for the project name, then click Create.

project_name.png

Open the Google Sheets API page, then double check if the correct project is selected.

select_project_dropdown.png

Once you have made sure that you are using the correct project, click Enable.

enable.png

On the left of the screen, click on Credentials.

credentials_tab.png

On the bar at the top, click on the Create credentials button, then select Service account.

create_service_account.png

Enter any name for the Service account name. You may also enter a description, as you see fit.

service_account_setup.png

Save the email address for later, as it will be required to add the service account to a Google Sheet.

Click Done, as granting permissions to this service account is not required.

To the right of the screen, open the menu on the newly created service account, and click Manage keys.

manage_keys.png

Click Add key, then Create new key.

create_new_key.png

Click Create key. Leave the type as JSON.

create_new_key_2.png

You will get a JSON file in your downloads directory.

Open the service account key JSON file in a text editor, and copy the full contents.

Open the Environment Variables page, then click New env variable.

Set the key to a name like google_service_account, then paste the entire JSON data into the value.

Once you are finished, click Add.

env.png

Open Google Sheets and create a new empty sheet or open an existing one.

Click the Share button, then paste your service account's email into the dialog. Make sure it is added as an editor, and optionally disable "Notify people".

share.png

Lastly, copy the sheet ID from the URL bar. It's the long string of characters between /d/ and /edit.

sheet_id.png

To interact with the Google Sheets API, use the @mattx.gsheet_call wrapper.

This automates requesting an access token from Google to access the Google Sheets API.

It requires 4 arguments:

Here is an example of what the above looks like when put together:

import { gsheet_call } from "https://esm.town/v/mattx/gsheet_call";

// Appending to a sheet
await gsheet_call(
  Deno.env.get("google_sa"),
  "1LDgOhO6Fxg2wt5rGFH29t6XYbmKe6fXI7fLSFaqZkDA",
  "POST",
  "values/A1:C1:append?valueInputOption=RAW",
  { values: [[Date(), Math.random(), 1]] },
);

// Reading from the top of the sheet
const data = await gsheet_call(
  Deno.env.get("google_sa"),
  "1LDgOhO6Fxg2wt5rGFH29t6XYbmKe6fXI7fLSFaqZkDA",
  "GET",
  "values/A1:C1?majorDimension=ROWS",
  {},
);
console.log(data["values"][0]);

sheet_output.png

Last updated