CODEWITHSUNDEEP
node.jsgoogle-sheets-api
Mascarpone
Mascarpone

Reputation: 2556

Update a SpreadSheet with the google API

I'm trying to update a given spreadsheet cell, using node.js googleapis v4 and authorizing with a JWT client.

Reading works fine, but I cannot understand how to write: 

    new Promise((resolve, reject) => {

            sheets.spreadsheets.values.update({
                auth: this._auth,
                spreadsheetId: this._metaData.spreadSheetId,
                range: range,
                valueInputOption: 'USER_ENTERED'
            },(err, resp) => {

                if (err) {
                    console.log('Data Error :', err)
                    reject(err);
                }

                resolve(resp);

            });

        });

How do I specify the data, and how do I pass it to the call?

I understand I should use a ValueRange object, but how?

Upvotes: 8

Views: 7696

Answers (2)

Adrian Molina
Adrian Molina

Reputation: 89

This code works to update a spreadsheet cell, look at edit method

import { JWT } from 'google-auth-library';
import { GoogleSpreadsheet } from 'google-spreadsheet';

const GOOGLE_DOCS_CLIENT_EMAIL = 'service account email';
const GOOGLE_DOCS_KEY = 'your key here';
const SPREADSHEET_ID = 'spreadsheet id';

const serviceAccountAuth = new JWT({
  email: GOOGLE_DOCS_CLIENT_EMAIL,
  key: GOOGLE_DOCS_KEY,
  scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});
const doc = new GoogleSpreadsheet(
  SPREADSHEET_ID,
  serviceAccountAuth,
);
const getCellCoordinates = (cell: string) => {
  const row = cell.charCodeAt(0);
  const column = cell.substring(1);
  return {
    row: row - 65,
    column: parseInt(column, 10) - 1,
  };
};
const edit = async() => {
  await doc.loadInfo();
  const sheet = doc.sheetsByTitle['sheetName'];
  await sheet.loadCells('A1:C1');
  const {row, column} = getCellCoordinates('A1');
  sheet.getCell(row, column).value = 'testing value';
  await sheet.saveUpdatedCells();
}
(async () => {
  await edit();
})();

Upvotes: 0

Mascarpone
Mascarpone

Reputation: 2556

After better reviewing the (poor) documentation, I inferred that you had to pass a request object in the call:

return new Promise((resolve, reject) => {
  sheets.spreadsheets.values.update(
    {
      auth: this._auth,
      spreadsheetId: this._metaData.spreadSheetId,
      range: "Sheet1!A1",
      valueInputOption: "USER_ENTERED",
      resource: { range: "Sheet1!A1", majorDimension: "ROWS", values: [["b"]] },
    },
    (err, resp) => {
      if (err) {
        console.log("Data Error :", err);
        reject(err);
      }
      resolve(resp);
    }
  );
});

Upvotes: 24

Related Questions