Nodejs Google Sheets API

Question

Does anyone know where to find a good example of using the Google Sheets API to write to a google sheet? I've used https://developers.google.com/sheets/api/quickstart/nodejs, and was successfully to able to read from a Google Sheets, but am having trouble figuring out how to write to a Google Sheets.

Answer 1

The below works for me, assuming you have already gotten an authorized oAuth2Client.

const { google } = require('googleapis');
const sheets = google.sheets({ version: 'v4', oAuth2Client});

async function writeSample() {
  let title = "NewSpreadsheet";
  try {
    var spreadsheet = await createSheet(title);
    console.log(`Spreadsheet ID: ${spreadsheet.spreadsheetId}`);

    range = "Sheet1!A1:D5";
    let valueInputOption = "RAW";
    values = [
      ["Item", "Cost", "Stocked", "Ship Date"],
      ["Wheel", "$20.50", "4", "3/1/2016"],
      ["Door", "$15", "2", "3/15/2016"],
      ["Engine", "$100", "1", "30/20/2016"],
      ["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
    ];
    var result = await writeSheet(spreadsheet.spreadsheetId, range, valueInputOption, values);
    console.log('%d cells updated.', result.updatedCells);
    return result;
  }
  catch (error) {
    console.log(error);
  }
}

async function createSheet(title) {
  var resource = {
    properties: {
      title
    },
  };
  return sheets.spreadsheets.create({ resource, fields: 'spreadsheetId', })
    .then(response => response.data);
}

async function writeSheet(spreadsheetId, range, valueInputOption, values, ) {
  resource = {
    values
  };
  return sheets.spreadsheets.values.update({ spreadsheetId, range, valueInputOption, resource, })
    .then(response => response.data);
}

Answer 2

Sheets API has sample with regard to Writing a single range on google sheets. There are concepts you must understand though like A1 Notation.

Aside from the samples from the guide, I also wrote JS codes to demonstrate writing on sheets on this SO thread. This will be helpful as NodejS uses JS too.