Google Sheets

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.replace(/\\n/g, '\n'),
    },
    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
  });
};

Usage

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.

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).

To securely access the API, we need to include the secret with each request. We also do not want to commit secrets to git. Thus, we should use an environment variable. Learn how to add environment variables in Vercel.