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.

Answer 3

Yes, I have also read noogui's references: it will take some time before you have a finished program.

But I have succeeded in doing the following functions:

    appedData ()
    deleteRow () (I can post them if you wish)

I needed to insert: temp, humidity and pressure in a row and in combination delete the oldest row (row 1). I placed these two functions in the same line where listMajors(...) was originally (in quickstart, js). And then put it into a loop: Bad idea: The functions are asynchronous so you do not know when one is finished and the other begins, A console.log() print showed:

 appedData ()
 deleteRow ()
 appedData ()
 deleteRow ()
 deleteRow ()
 appedData ()
 deleteRow ()
 appedData ()
 appedData ()

So sometimes appedData() and deleteRow() in real order, sometimes two or more times appedData() in order And other times two or more times deleteRow() in order.

I therefore changed quickstart.js to synchronous function instead of asynchronous: I removed callbacks of replaced fs.readFile with fs.readFileSync, And it seems to work