comparing json array rows with value on destination rows (apps script)

Far 0
Viewed 71

I've use this script before, and work well, here's script, This script copies the values and paste to last row in google sheet,

function doPost(request = {}) {
  const { parameter, postData: { contents, type } = {} } = request; //request data
  const { dataReq = {} } = JSON.parse(contents); //content
  const { fname = {} } = JSON.parse(contents); //function name

  const response = {
    status: "function not found: " + fname, // prepare response in function not found
    data2: dataReq
  }
  switch (fname) { //function selection
    case 'pasteData':
      var output = JSON.stringify(pasteDAta(dataReq)) //call function with data from request
      break
    default:
      var output = JSON.stringify(response)
      break
  }
  return ContentService.createTextOutput(output).setMimeType(ContentService.MimeType.JSON); //response to frontend
}
function pasteDAta(dataReq) {
  const id = '1_27rjNQmlXrwVKpLWUbGrJYPJufGRa7Dk-XEKcNAHr0'; //id of Google Sheet
  var sheet = SpreadsheetApp.openById(id).getSheetByName('Sheet1'); //sheet
  var headings = sheet.getDataRange().getValues()[0]; //Headers
  var values = dataReq.map((a) => {
  let holder = [];
  for (x in headings) {
    let output = (headings[x] in a) ? a[headings[x]] : '';
    holder.push(output);
  }
  return holder;
});
var len = values.length;
sheet.getRange(sheet.getLastRow() + 1, 1, len, values[0].length).setValues(values);
return "Numbers of sheets added: " + len;
}

I want this script to be able to check the row values ​​in column b (source [json]), if the value in the source rows in column b is the same as the value in the destination rows in column b (google sheet) then the values ​​are replaced all but if not, the values ​​are copied to the last rows. If it is possible, can anyone give me a modified working script?

Example

  1. First condition; Before (Destination Sheet)
Date Code Name Grade
02/04/21 Math1 John 80
02/04/21 Math2 John 80

Expected results After replacing (from JSON)- if Column B (Code) is the same as the source

Date Code Name Grade
02/04/21 Math1 Dare 78
02/04/21 Math2 Brian 90
  1. Second condition; Before (Destination Sheet)
Date Code Name Grade
02/04/21 Bio1 Anton 78
02/04/21 Bio2 Julian 65

Expected results After after appending to last row (from JSON)- if Column B isn't same as the source

Date Code Name Grade
02/04/21 Bio1 Anton 78
02/04/21 Bio2 Julian 65
02/04/21 Math1 Dare 78
02/04/21 Math2 Brian 90
web-applications google-apps-script json arrays
1 Answers

Try to change the function pasteDAta() this way:

function pasteDAta(dataReq) {
  const id = "1_27rjNQmlXrwVKpLWUbGrJYPJufGRa7Dk-XEKcNAHr0";
  var sheet = SpreadsheetApp.openById(id).getSheetByName("Sheet1");

  // get header and the rest data from the sheet
  var [ headings, ...data ] = sheet.getDataRange().getValues();

  var values = dataReq.map((a) => {
    let holder = [];
    for (x in headings) {
      let output = headings[x] in a ? a[headings[x]] : "";
      holder.push(output);
    }
    return holder;
  });
  var len = values.length;

  var new_values = []; // values to add at the bottom of the sheet
  var col_b = data.map(x => x[1]);  // get column B from the data

  values.forEach(row => {
    // find an index of the row with the same value in cell B
    var row_index = col_b.indexOf(row[1]);

    // if nothing was found add the row to the new values
    if (row_index == -1) new_values.push(row); 

    // else change the found row on the sheet
    else sheet.getRange(row_index + 2, 1, 1, row.length).setValues([row]); // '+2' due the header
  }) 

  // add the new values at the bottom of the sheet
  if (new_values.length > 0)
    sheet.getRange(sheet.getLastRow() + 1, 1, len, new_values[0].length).setValues(new_values);

  return "Numbers of sheets added: " + len;
}
Yuri Khristich0
Related