(TBD) spreadsheet api example

index.ts

/**
 * After aquired a service account's certfication with right permisstion with spreadsheet
 */
import { google } from "googleapis"; // npm i googleapis
const serviceAccount = require('service-account-cert.json')
const auth = new google.auth.JWT(
  serviceAccount.client_email,
  undefined,
  serviceAccount.private_key,
  "https://www.googleapis.com/auth/spreadsheets",
  undefined
);
const sheets = google.sheets("v4");

const spreadsheetId = "example"

async function createSheet(title) {
  const spreadsheet = await sheets.spreadsheets.get({
    auth,
    spreadsheetId,
    includeGridData: true
  });
  if (!spreadsheet.data.sheets) {
    throw new Error("Sheets not found!");
  }
  const targetSheet = spreadsheet.data.sheets.find(
    s => s.properties && s.properties.title === title
  );
  if (!targetSheet) {
    await sheets.spreadsheets.batchUpdate({
      auth,
      spreadsheetId,
      requestBody: {
        requests: [{ addSheet: { properties: { title } } }]
      }
    });
    console.log(`Create sheet: ${title}`);
  }
}

async function updateSheet(title: string) {
  const values = [[new Date()]];
  const updateResult = await sheets.spreadsheets.values.update({
    auth,
    spreadsheetId,
    range: `${title}`, // or ${title}`A1!
    valueInputOption: "USER_ENTERED",
    requestBody: {
      majorDimension: "ROWS",
      values
    }
  });
  if (updateResult.status === 200) {
    console.log("Update success" );
  } else {
    console.error(updateResult.data);
    console.error("Update failed");
  }
}