Google Sheets

Create an entry in a sheet.

GS
import { google } from 'googleapis';

export default async (req, res) => {
  const auth = new google.auth.GoogleAuth({
    credentials: {
      client_email: process.env.GOOGLE_CLIENT_EMAIL,
      client_id: process.env.GOOGLE_CLIENT_ID,
      private_key: process.env.GOOGLE_PRIVATE_KEY
    },
    scopes: [
      'https://www.googleapis.com/auth/drive',
      'https://www.googleapis.com/auth/drive.file',
      'https://www.googleapis.com/auth/spreadsheets'
    ]
  });

  const sheets = google.sheets({
    auth,
    version: 'v4'
  });

  const response = await sheets.spreadsheets.values.append({
    spreadsheetId: '1S0dHGWsymzIzUdzc3JE4d8MVjpXcj4uc_4LYafGtG44',
    range: 'A1:C1',
    valueInputOption: 'USER_ENTERED',
    requestBody: {
      values: [
        ['01/01/2020', 'Bob Smith', '$100'],
        ['02/02/2020', 'Jane Doe', '$200']
      ]
    }
  });

  return res.status(201).json({
    data: response.data
  });
};
import { google } from 'googleapis';

exports.handler = async (event, context, callback) => {
  const auth = new google.auth.GoogleAuth({
    credentials: {
      client_email: process.env.GOOGLE_CLIENT_EMAIL,
      client_id: process.env.GOOGLE_CLIENT_ID,
      private_key: process.env.GOOGLE_PRIVATE_KEY
    },
    scopes: [
      'https://www.googleapis.com/auth/drive',
      'https://www.googleapis.com/auth/drive.file',
      'https://www.googleapis.com/auth/spreadsheets'
    ]
  });

  const sheets = google.sheets({
    auth,
    version: 'v4'
  });

  const response = await sheets.spreadsheets.values.append({
    spreadsheetId: '1S0dHGWsymzIzUdzc3JE4d8MVjpXcj4uc_4LYafGtG44',
    range: 'A1:C1',
    valueInputOption: 'USER_ENTERED',
    requestBody: {
      values: [
        ['01/01/2020', 'Bob Smith', '$100'],
        ['02/02/2020', 'Jane Doe', '$200']
      ]
    }
  });

  return {
    statusCode: 201,
    body: JSON.stringify({
      data: response.data
    })
  };
};

Usage#

1

Get Access To Sheets

To gain access to the Google Sheets API, you need to:

  • Go to the Google Developer Console.
  • Create a new project.
  • Credentials -> Create Credentials -> Service-account key.
  • Click “Enable APIs and services.”
  • Find "Google Sheets API" and enable it.
2

Create Service Account

Since we're communicating server-to-server, we'll need a service account.

  • Go to your service accounts page.
  • Create a service account.
  • Click "Create Key" and choose JSON.

You should now have a JSON file similar to this:

{
  "type": "service_account",
  "project_id": "...",
  "private_key_id": "...",
  "private_key": "...",
  "client_email": "...",
  "client_id": "...",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "..."
}

Save this file. You will need these values for environment variables. Okay, back to the service account. We need to delegate domain-wide authority.

  • On the table row, click Actions -> Edit.
  • Show Domain-Wide Delegation -> Enable G-Suite Domain-Wide Delegation.

Finally, we need to grant edit permission for our sheet.

  • Open your Google Sheet.
  • In the top right, click "Share".
  • Grant edit permission to your service account email (or make it public).
3

Add Environment Variables

To securely access the API, we need to include the secrets with each request. We also do not want to commit secrets to git. Thus, we should use environment variables.

  • Netlify: Settings -> Build & deploy -> Environment -> Environment variables.
  • Vercel: Settings -> General -> Environment Variables.

Alternatively, you can add the enviornment variables to your vercel.json or netlify.toml file.

New functions in your inbox

Get access to functions when they're added.