How to append row to Sheets API if it doesn't exists, otherwise update it-CodePudding

in JavaScript I am trying to use Google Sheets API. What I am trying to do is to append a row if it doesn't already exists in the spreadsheets, but update it if it is duplicated.

My sheets :

IDS	Status
A1	Status1
A2	Status2

What I want to push is : [A1, Status2];[A3, Status1]

So in this case, A1 Status is gonna change, and A3 is added as a new row.

(I already get in the array I want to push the IDs with (and the Status in another way):

const getIds = googleSheets.spreadsheets.values.get({
        spreadsheetId,
        range: "'Sheet 1'!A3:A23",
        auth,
    });

CodePudding user response：

I believe your goal is as follows.

Your "Sheet1" of Spreadsheet is in the table of your question.
- In your script, you are using the range as 'Sheet 1'!A3:A23. But, in your table, it seems that the data is shown from row 2.
You have the input value like [["A1", "Status2"], ["A3", "Status1"]].
You want to update "Sheet1" using the input value. When your showing table and your input value are used, you want to achieve the following situation of "Sheet1".
```
  IDS  Status
  A1   Status2
  A2   Status2
  A3   Status1
```
From your added tag, you want to achieve this using googleapis for Node.js.
You have already been able to get and put values to the Spreadsheet using Sheets API.

In this case, how about the following sample script?

Sample script:

const sheets = google.sheets({ version: "v4", auth }); // Please use your authorization.
const spreadsheetId = "###"; // Please set your Spreadsheet ID.
const inputValues = [["A1", "Status2"], ["A3", "Status1"]]; // This is from your question.

sheets.spreadsheets.values.get({spreadsheetId, range: "'Sheet 1'!A2:B"},
  (err, { data: { values } }) => {
    if (err) {
      console.log(err);
      return;
    }
    const obj1 = inputValues.reduce((o, e) => ((o[e[0]] = e), o), {});
    const obj2 = values.reduce((o, e) => ((o[e[0]] = e), o), {});
    const res = [
      ...values.map((e) => obj1[e[0]] || e),
      ...inputValues.reduce((ar, e) => (obj2[e[0]] || ar.push(e), ar), []),
    ];
    sheets.spreadsheets.values.update(
      {
        spreadsheetId,
        range: "'Sheet 1'!A2",
        resource: { values: res },
        valueInputOption: "USER_ENTERED",
      },
      (err, { data }) => {
        if (err) {
          console.log(err);
          return;
        }
        console.log(data);
        }
      }
    );
  }
);

In this sample script, first, the values are retrieved from the columns "A" and "B" of "Sheet1". And, the retrieved values are updated using the input value. And, the updated values are put to "Sheet1".

Note:

In your showing script, it seems that the sheet name is Sheet 1 which is not Sheet1. So, please confirm your sheet name again.

Sample script:

Note:

References: