Update a row in google sheets based on duplicate

Question

I'm designing a script that takes an object (jsonData[data]) and inputs its values into a different sheet based on which product it is.

Currently the script inputs all the data into a new row each time the form reaches a new stage, however the form goes through 4 stages of approval and so I'm finding each submission being entered into 4 different rows. Each submission has an "Id" value within the object which remains the same (but each submission could also be on any row in the sheet as it's used a lot).

I'm checking whether the ID exists in the sheet and using iteration to find the row number:

function updatePlatformBulkInfo(jsonData) {  
  var sheetUrl = "https://docs.google.com/spreadsheets/d/13U9r9Lu2Fq1WTT8pQ128heCm6_gMmH1R4O6u8e7kvBo/edit#gid=0"; 
  var sheetName = "PlatformBulkSetup";  
  var doc = SpreadsheetApp.openByUrl(sheetUrl);  
  var sheet = doc.getSheetByName(sheetName);
  var rowList = [];
  var formId = jsonData["Id"];
  var allSheetData = sheet.getDataRange().getValues();
  setLog("AllSheetData = " + allSheetData[1][11]) //Logs to ensure data is collected correctly
  var rowEdited = false;
  var rowNumber = 0;

  //Check whether ID exists in the sheet  
  for (var i = 0; i < allSheetData.length; i++) {
    if(allSheetData[i][11] == formId) {
      rowEdited = true;
    } else {
        rowNumber += 1;        
    } 
  }

My issue is with the next part:

//Append row if ID isn't duplicate or update row if duplicate found
if (rowEdited == false) {
  for (var data in jsonData) {
    rowList.push(jsonData[data])
  } 
  setLog("***Row List = " + rowList + " ***");
  setLog("***Current Row Number = " + rowNumber + " ***");
  sheet.appendRow(rowList); 
} else if(rowEdited == true){
  var newRowValue = jsonData[data];
  sheet.getRange(rowNumber, 1).setValues(newRowValue);
}

Everything works fine if the duplicate isn't found (the objects values are appended to the sheet). But if a duplicate is found I'm getting the error:

Cannot find method setValues(string)

This looks to me like i'm passing a string instead of an object, but as far as I'm aware I've already converted the JSON string into an object:

var jsonString = e.postData.getDataAsString();
var jsonData = JSON.parse(jsonString);

How can I modify my script to write the updated data to the matched row?

what do you see when you do console.log(sheet.getRange(rowNumber, 1)) ? — Nope
setValues takes an Object[][] that will tile in the bound range. i.e an array of arrays. — tehhowch
console.log(sheet.getRange(rowNumber, 1)) just returns 'Range' — Adam Roberts
@tehhowch I only need the data from the object rather than an Object[][] (as I'm only moving the values that have been inputted into the sheet - is there any other way to do it? — Adam Roberts
@AdamRoberts given that you want to write to a single cell a single value, you want to use Range#setValue() and not Range#setValues() — tehhowch

tehhowch tehhowch · Accepted Answer · 2018-03-28T14:53:24

It's unclear based on your code whether or not you will actually write to the correct cell in the case of a duplicate. As presented, it looks as though you loop over the sheet data, incrementing a row number if the duplicate is not found. Then, after completing the loop, you write to the sheet, in the row described by rowNumber, even though your code as written changes rowNumber after finding a duplicate.

To address this, your loop needs to exit upon finding a duplicate:

var duplicateRow = null, checkedCol = /* your column to check */;
for(var r = 0, rows = allSheetData.length; r < rows; ++r) {
  if(allSheetData[r][checkedCol] === formId) {
    // Convert from 0-base Javascript index to 1-base Range index.
    duplicateRow = ++r;
    // Stop iterating through allSheetData, since we found the row.
    break;
  }
}

In both cases (append vs modify), you seem to want the same output. Rather than write the code to build the output twice, do it outside the loop. Note that the order of enumeration specified by the for ... in ... pattern is not dependable, so if you need the elements to appear in a certain order in the output, you should explicitly place them in their desired order.
If a duplicate ID situation is supposed to write different data in different cells, then the following two snippets will need to be adapted to suit. The general idea and instructions still apply.

var dataToWrite = [];
/* add items to `dataToWrite`, making an Object[] */

Then, to determine whether to append or modify, test if duplicateRow is null:

if(dataToWrite.length) {
  if(duplicateRow === null) {
    sheet.appendRow(dataToWrite);
  } else {
    // Overwriting a row. Select as many columns as we have data to write.
    var toEdit = sheet.getRange(duplicateRow, 1, 1, dataToWrite.length);
    // Because setValues requires an Object[][], wrap `dataToWrite` in an array.
    // This creates a 1 row x N column array. If the range to overwrite was not a
    // single row, a different approach would be needed.
    toEdit.setValues( [dataToWrite] );
  }
}

Update a row in google sheets based on duplicate

2 Answers