I am working on an Nextjs application that uses a Google Spreadsheet as a database. The application should GET, APPEND and UPDATE values in a spreadsheet, using the Sheets API v4.I have difficulties updating a specific row. I need to find a row that has a specific value in it's first column ("ID") and update all the cells in this row.

I want to mark attendance for the Students

This is how my spreadsheet looks like.

I have tried removing all values and looping the array and getting the key of the matching value. I want to use and search or find options from google sheet API.

I tried the implement google sheet API to use batchUpdateByDataFilter()

import { google } from "googleapis";
const auth = new google.auth.GoogleAuth({
    credentials: {
      client_email:
        "[email protected]", //Placeholder client_email value
      private_key:
        "-----BEGIN PRIVATE KEY-----\n-----END PRIVATE KEY-----\n", //Placeholder private_key value
    },
    scopes: [
      "https://www.googleapis.com/auth/drive",
      "https://www.googleapis.com/auth/drive.file",
      "https://www.googleapis.com/auth/spreadsheets",
    ],
  });

  const sheets = google.sheets({
    auth,
    version: "v4",
  });

  const sourceSheet = await sheets.spreadsheets.values.batchUpdateByDataFilter({
      spreadsheetId: "", //Placeholder private_key value
      requestBody: {
        valueInputOption: "USER_ENTERED",
        data: [
          {
            dataFilter: {
              developerMetadataLookup: {
                locationType: "COLUMN",
                metadataKey: "id",
                metadataValue: "30336547",
                visibility: "DOCUMENT",
                metadataLocation: {
                  locationType: "COLUMN",
                  spreadsheet: false,
                },
                metadataId: 0,
              },
            },
            values: [["Present"]],
          },
        ],
      },
    });

  res.status(200).json({
    success: true,
    msg: sourceSheet,
  });

CodePudding user response：

I believe your goal is as follows.

• You want to search for a value from column "A" of Google Spreadsheet and want to update column "C".
• You want to achieve this using googleapis for node.js.

Modification points:

• In the current stage, the values cannot be directly searched with REST Resource: spreadsheets.developerMetadata.
• In this case, it is required to do the following flow.
  1. Retrieve values from Spreadsheet.
  2. Check the values.
  3. Update the Spreadsheet.

When this flow is reflected in your script, how about the following modification? In this modification, the script for using Sheets API is modified.

Modified script:

const sheets = google.sheets({auth, version: "v4"}); // This is from your showing script.

const spreadsheetId = "###"; // Please set your Spreadsheet ID.
const sheetName = "Sheet1"; // Please set your sheet name.
const inputValues = ["30336551", "30336553"]; // This is a sample input value.

const { data: { values }} = await sheets.spreadsheets.values.get({ spreadsheetId, range: sheetName });
await sheets.spreadsheets.values.update({
  spreadsheetId,
  range: sheetName,
  resource: {values: values.map((r) => inputValues.includes(r[0]) ? [r[0], r[1], "Present"] : r)},
  valueInputOption: "USER_ENTERED",
});

• When this script is run, the rows of ["30336551", "30336553"] (These values are from your question.) in column "A" are updated. A value of "Present" is put into the column "C".

References:

• Method: spreadsheets.values.get
• Method: spreadsheets.values.update