Search code examples
javascriptgoogle-apps-scriptparameter-passingdelete-row

Using deleteRow when passing a parameter using Google Sheets with Client-side & Server-side

I am using Google's Web Apps for this code and I am a true novice when it comes to javascript. The way it works is that the user enters an id and once they click the delete button the value is passed to the server-side. Before it deletes I want it to copy the row over to another tab (that works) and then proceed to delete the row which is not working. Thanks in advance for the help. Below is the server side code.

function removeRecord(dataInfo) {
    var ss = SpreadsheetApp.openById(sheetID);
    var source = ss.getSheetByName("Table");
    var target = ss.getSheetByName("Deleted");
    var data = source.getRange(2, 1, source.getLastRow()-1, source.getLastColumn()).getValues();


    var id = data.map(function (r) { return r[0]; });
    
    var pos = id.indexOf(dataInfo);
    
    
    if (pos > -1) {
      target.appendRow(data[pos]);
      source.deleteRow(data[pos]);
    }
}
Solution

  • I think that the argument of deleteRow(rowPosition) is the integer number. But, in your script, an array is used. I think that this might be the reason for your current issue. In the case of your script, how about the following modification?

    From:

    source.deleteRow(data[pos]);

    To:

    source.deleteRow(pos + 2);

    Note:

    • From I am using Google's Web Apps, when you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.

    • You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".

    • As another approach, in your script, the following modification might be able to be used. In this case, TextFinder is used. By this, the process cost might be able to be reduced a little. By the way, in this script and the above modification, it supposes that dataInfo is the valid value. Please be careful about this.

      function removeRecord(dataInfo) {
  var ss = SpreadsheetApp.openById(sheetID);
  var source = ss.getSheetByName("Table");
  var search = source.getRange("A2:A" + source.getLastRow()).createTextFinder(dataInfo).matchEntireCell(true).findNext();
  if (!search) return;
  var target = ss.getSheetByName("Deleted");
  search.offset(0, 0, 1, source.getLastColumn()).copyTo(target.getRange(target.getLastRow() + 1, 1), { contentsOnly: true });
  source.deleteRow(search.getRow());
}

    Reference: