Maker-Spreadsheet

Simple way to talk to a spreadsheet in Javascript. Without OAauth but with a server token. Not a good practice to share your private key for public facing websites. So think twice about creating JWT tokens on the client.

Features

• Read a Google Spreadsheet
• Write to cells in a Google Spreadsheet
• Offline support, retries every 5 seconds

Usage

  // Generate JWT token some way.
  // https://jwt.io/libraries?language=JavaScript
  // Example is for jose, you could use any other JWT token method.

  // JWT method below is for a Service Account.
  // https://console.cloud.google.com/projectselector2/iam-admin/serviceaccounts?supportedpurview=project
  // Generate key -> copy JSON values in variablese below.

  import * as jose from 'jose';
  // required algoritm
  const alg = 'RS256';
  // private_key
  const pkcs8 = '-----BEGIN PRIVATE KEY-----\n...-----END PRIVATE KEY-----\n';
  const privateKey = await jose.importPKCS8(pkcs8, alg);
  const jwt = await new jose.SignJWT({
    // client_email
    iss: '',
    // client_email
    sub: '',
    // required url, sheets api
    aud: 'https://sheets.googleapis.com/',
    // UTC now
    iat: Math.floor(Date.now() / 1000),
    // UTC now + 1 hour
    exp: Math.floor(Date.now() / 1000) + 3600,
  })
    .setProtectedHeader({ alg })
    .sign(privateKey);

  // Now where this library comes into use
  const auth = new Auth(jwt);
  const spreadsheet_promise = auth.getSpreadsheet('...spreadsheet URL or ID...').load();
  const spreadsheet = await spreadsheet_promise; // or .then(spreadsheet => {...});

  const sheet = spreadhseet.sheet[0];
  const cell = sheet['A1'];
  // Tries the update.
  cell.value = 'New Value';