Update particular cells in a range using gsheet api and javascript

Question

I have a pure javascript front end webapp which I have managed to connect to the google sheet api to fetch the values from it, now I want to update selected set of cells. I am referring to code that is available https://github.com/gsuitedevs/browser-samples/blob/master/sheets/snippets/snippets.js. Code pasted below. This is working but it is straight way overriding the existing data sequentially, I want it to carry out selective batch update -

For example : range A:B has data

1   Apple
2   Orange
3   Pineapple

on running update function I want the following result

1   Apple
2   Pear
3   Pineapple

So just update row 2 instead of overriding. At the moment I get following output on my gsheet

2   Pear
2   Orange
3   Pineapple

If anyone can help me with this that will be great. If there is any other function that I can use to update this please suggest. Basically

My code is

 function batchUpdateValues(spreadsheetId, range, valueInputOption, _values, callback) {
    // [START sheets_batch_update_values]
    var values = [
      [
        [usedIdArray],
      ],

    ];
    // [START_EXCLUDE silent]
    values = _values;
    // [END_EXCLUDE]
    var data = [];
    data.push({
      range: range,
      values: values,
      majorDimension: "COLUMNS"
    });
    // Additional ranges to update.

    var body = {
      data: data,
      valueInputOption: valueInputOption

    };
    gapi.client.sheets.spreadsheets.values.batchUpdate({
      spreadsheetId: spreadsheetId,
      resource: body
    }).then((response) => {
      var result = response.result;
      console.log(`${result.totalUpdatedCells} cells updated.`);
      // [START_EXCLUDE silent]
      callback(response);
      // [END_EXCLUDE]
    });
    // [END sheets_batch_update_values]
  }

In order to correctly replicate your situation, can I ask you about the value of range and usedIdArray? — Tanaike
– Tanaike, Commented Jul 18, 2020 at 9:05
Hey @Tanaike thanks for your swift response. Sure - range is Sheet1!B:C and usedIdArray is an array of IDs from column B which I collected from the same sheet using - function getValues(spreadsheetId, range, callback) — pc372
– pc372, Commented Jul 18, 2020 at 9:16
Thank you for quick reply. From your replying, I would like to confirm your goal. In your Spreadsheet, the columns "B" and "C" have the ID and value, respectively. And you want to replace the value for the ID on Spreadsheet using the inputted values which have the IDs and values. Is my understanding correct? — Tanaike
– Tanaike, Commented Jul 18, 2020 at 9:20
Hey @Tanaike you are absolutely right - column B has keys and column C has values. In my getValues request I pull all the keys which have no values in the adjoining column C and before updating them, I have an array that has values to be added against those key. Again you are absolutely right sir :) — pc372
– pc372, Commented Jul 18, 2020 at 9:26
Thank you for replying. From your replying, I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your goal and that was not the direction you expect, I apologize. At that time, can you provide the detail information for achieving your goal. — Tanaike
– Tanaike, Commented Jul 18, 2020 at 23:55

Tanaike · Accepted Answer · 2020-07-22 23:57:38Z

1

I believe your goal and situation as follows.

You want to replace the values of the column "C" when the keys of the column "B" in Sheet1 include the keys of the inputted values.
You want to append the inputted values to Sheet1 when the keys of the column "B" in Sheet1 doesn't include the keys of the inputted values.
You want to achieve this using googleapis for Javascript.
You have already get and put values for Google Spreadsheet using Sheets API.

In order to achieve above, I would like to propose the following modification.

Modification points:

In your script, the inputted values are directly put to the Spreadsheet. By this, the existing values are not replaced.
In order to achieve your goal, I would like to propose the following flow.
1. Retrieve the existing values from "Sheet1" in the Spreadsheet using the method of values.get in Sheets API.
2. Create the updated values using the retrieved values.
3. Put the updated values to "Sheet1" using your script.

When this flow is reflected to your script, it becomes as follows.

Modified script:

function batchUpdateValues(spreadsheetId, range, valueInputOption, _values, callback) {
  // 1. Retrieve the existing values from "Sheet1" in the Spreadsheet using the method of values.get in Sheets API.
  gapi.client.sheets.spreadsheets.values.get({spreadsheetId: spreadsheetId, range: range})
  .then(function(response) {

    // 2. Create the updated values using the retrieved values.
    let values = response.result.values;
    const obj = values.reduce((o, [b], i) => Object.assign(o, {[b]: i}), {});
    const addValues = _values.reduce((ar, [b, c]) => {
      if (obj[b]) {
        values[obj[b]][1] = c;
      } else {
        ar.push([b, c]);
      }
      return ar;
    }, []);
    values = values.concat(addValues);

    // 3. Put the updated values to "Sheet1" using your script.
    var data = [];
    data.push({range: range, values: values});
    var body = {data: data, valueInputOption: valueInputOption};
    gapi.client.sheets.spreadsheets.values.batchUpdate({spreadsheetId: spreadsheetId,resource: body})
    .then((response) => {
      var result = response.result;
      console.log(`${result.totalUpdatedCells} cells updated.`);
      callback(response);
    });
  }, function(reason) {
    console.error('error: ' + reason.result.error.message);
  });
}

Reference:

Method: spreadsheets.values.get

Added:

From your replying and shared Spreadsheet, I could understand like below.

Thare is IDs in the column "B" and values in the column "C".
When the IDs are 1 to 17, you want to replace and put the values like below.
- 2 reader 4 gloom 8 jest 10 honor 11 contribution 12 session 13 money 14 opinion 15 steward 16 runner 17 feminist
- In above case, 2, 4, 8,,, is the IDs. The string values are the values you want to put.

Sample script:

From your replying, I cannot understand about the input values you expect. So in this sample script, I propose the input values from the values in your repling.

In this case, the input values for the function batchUpdateValues is as follows.

const spreadsheetId = "###";  // Spreadsheet ID
const range = "Sheet1!B2:C";  // Range: In this case, the header row is not included.
const _values = {"2": "reader", "4": "gloom", "8": "jest", "10": "honor", "11": "contribution", "12": "session", "13": "money", "14": "opinion", "15": "steward", "16": "runner", "17": "feminist"};  // Your input values.
const valueInputOption = "USER_ENTERED";

The function batchUpdateValues is as follows.

function batchUpdateValues(spreadsheetId, range, valueInputOption, _values, callback) {
  gapi.client.sheets.spreadsheets.values.get({spreadsheetId: spreadsheetId, range: range})
  .then(function(response) {
    let values = response.result.values;
    values.forEach(e => {
      if (_values[e[0]]) e[1] = _values[e[0]];
    });

    // If the input ID is not existing in the Spreadsheet, the ID and value is appended using this section.
    const obj = values.reduce((o, [b]) => Object.assign(o, {[b]: true}), {});
    const r = Object.entries(_values).filter(([k]) => !obj[k]);
    values = values.concat(r);

    var data = [];
    data.push({range: range, values: values});
    var body = {data: data, valueInputOption: valueInputOption};
    gapi.client.sheets.spreadsheets.values.batchUpdate({spreadsheetId: spreadsheetId,resource: body})
    .then((response) => {
      var result = response.result;
      console.log(`${result.totalUpdatedCells} cells updated.`);
      callback(response);
    });
  }, function(reason) {
    console.error('error: ' + reason.result.error.message);
  });
}

edited Jul 22, 2020 at 23:57

answered Jul 18, 2020 at 23:55

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

pc372 Over a year ago

Tanks a lot @Tanaike. I will look to implement this and come back to you :). On the face this looks like a solution that I was after. I will get back to you for sure. Many thanks for a such a swift response.

Tanaike Over a year ago

@pc372 Thank you for replying. At first, when you test this script, please test it for the simple situation. Because I tested this for the simple situation as my understanding and I'm not sure about your actual whole script. Please be careful this.

pc372 Over a year ago

Hey @Tanike - the solution is alsmost working fine - it's just that it is overriding data already written. I think this line is reponsible for that " const obj = values.reduce((o, [b], i) => Object.assign(o, {[b]: i}), {});" Should I be taking my own array instead of "o" here?

pc372 Over a year ago

So for example at the moment if there are 32 new rows to be added, it writes 16 rows perfectly fine and then next 16 are overridden on the earlier 16.

Tanaike Over a year ago

@pc372 Thank you for replying. I apologize for the inconvenience and apologize for my poor English skill. Unfortunately, I cannot understand about your replying. So in order to correctly understand about your goal, can you provide the sample Spreadsheet including the input and output you expect? By this, I would like to confirm it and modify the script.

|

Collectives™ on Stack Overflow

Update particular cells in a range using gsheet api and javascript

1 Answer 1

Modification points:

Modified script:

Reference:

Added:

Sample script:

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

Reference:

Added:

Sample script:

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related