Update whole sheet when another sheet changes (script)

Question

I have a Google Spreadsheet that contains names of papers on the main sheet (called "ABSTRACTS"). One of the columns indicates if the paper has been discussed in the lab or not ("si","no"). When the column says "si" (yes) the script copies the papers name and info to another sheet called "Seminarios" (seminars), if the column says "no" it copies the info to another sheet called "DISCUTIR" (to discuss). The script works fine (it copies the info to the appropriate sheet) but it has a little problem. When we discuss a paper and I change its status from "no" to "yes" it adds it to the SEMINARIO sheet but it doesn't remove it from the "DISCUTIR" one. Is there a way to make the script refresh the whole sheet? (If I delete the entire content of the "DISCUTIR" sheet it adds only the ones that I want it to, but I want to find a way of doing it automatically).

Here I attach the code that I'm using. I'm sure its really untidy and inefficient since I have no idea of what I'm doing so I apologize for that. I have some programming experience in MATLAB but never used JavaScript and just managed to make it work by copying and pasting code.

function onEdit(e) {
  var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ABSTRACTS");
  var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SEMINARIOS");
  var sheetTo2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DESCARTADOS");
  var sheetTo3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DISCUTIR");
  var sheetTo4 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CHEQUEAR");

  var data = sheetFrom.getDataRange().getValues();
  var target = new Array(); // this is a new array to collect data
  for (n = 0; n < data.length; ++n) { // iterate in the array, row by row
    if (data[n][8] == "si") { // if condition is true copy the whole row to target
      target.push(data[n]); // copy the whole row
    } //if
  } //for

  //Paste to another sheet from first cell onwards
  sheetTo.getRange(2, 1, target.length, target[0].length).setValues(target);

  var data = sheetFrom.getDataRange().getValues();
  var target = new Array(); // this is a new array to collect data
  for (n = 0; n < data.length; ++n) { // iterate in the array, row by row
    if (data[n][8] == "descartado") { // if condition is true copy the whole row to target
      target.push(data[n]); // copy the whole row
    } //if
  } //for

  //Paste to another sheet from first cell onwards
  sheetTo2.getRange(2, 1, target.length, target[0].length).setValues(target);

  var data = sheetFrom.getDataRange().getValues();
  var target = new Array(); // this is a new array to collect data
  for (n = 0; n < data.length; ++n) { // iterate in the array, row by row
    if (data[n][8] == "no") { // if condition is true copy the whole row to target
      target.push(data[n]); // copy the whole row
    } //if
  } //for

  //Paste to another sheet from first cell onwards
  sheetTo3.getRange(2, 1, target.length, target[0].length).setValues(target);

  var data = sheetFrom.getDataRange().getValues();
  var target = new Array(); // this is a new array to collect data
  for (n = 0; n < data.length; ++n) { // iterate in the array, row by row
    if (data[n][8] == "chequear") { // if condition is true copy the whole row to target
      target.push(data[n]); // copy the whole row
    } //if
  } //for

  //Paste to another sheet from first cell onwards
  sheetTo4.getRange(2, 1, target.length, target[0].length).setValues(target);

}

Mattias Martens Mattias Martens · Accepted Answer · 2019-03-06T22:13:36

I think you can see why the row is not disappearing, but it's because you're only overwriting the data up to the final row of your new input data, and are not doing anything to the rows after that.

The docs shows that there is a function called clear() that will get rid of anything that was in there previously: https://developers.google.com/apps-script/reference/spreadsheet/sheet#clear

Calling sheetTo4.clear() will clear all contents and formatting, while calling sheetTo4.clear({contentsOnly: true}) will clear only the contents. You will of course have to rewrite the headers for the sheet.

Update whole sheet when another sheet changes (script)

1 Answers