CODEWITHSUNDEEP
typescriptgoogle-apps-script
Mohammad Haroon
Mohammad Haroon

Reputation: 33

Google script Delete Button not functioning properly

**I am trying to delete the row from google Sheets and I have assigned the script to this button but it not deleting the record when I click the delete button. it gives the message Reacod not found.I have also made a search button which is running properly **

function deleteRow() {

var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet

var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet

var datasheet = myGooglSheet.getSheetByName("SUPPORTING SHEET"); ////delcare a variable and set with the Database worksheet

//to create the instance of the user-interface environment to use the message box features var ui = SpreadsheetApp.getUi();

// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also // close the dialog by clicking the close button in its title bar.

var response = ui.alert("Delete", 'Do you want to delete the record?',ui.ButtonSet.YES_NO);

var str = shUserForm.getRange("E13").getValue();

var values = datasheet.getDataRange().getValues(); //getting the entire values from the used

range and assigning it to values variable

var valuesFound=false; //variable to store boolean value to validate whether values found or not

for (var i = 0; i<values.length; i++) { var rowValue = values&[i]; //declaraing a variable and storing the value

//checking the first value of the record is equal to the search item
if (rowValue[0]==str) {

  var  iRow = i+1;
  datasheet.deleteRow(iRow);

  ui.alert("Record Deleted!");

  shUserForm.getRange("E13").clear();
  shUserForm.getRange("E15").clear();
  shUserForm.getRange("E17").clear();
  shUserForm.getRange("E19").clear();
  shUserForm.getRange("E21").clear();
  shUserForm.getRange("E23").clear();
  shUserForm.getRange("E27").clear();
  shUserForm.getRange("E29").clear();
  shUserForm.getRange("E31").clear();
  shUserForm.getRange("E37").clear();
  shUserForm.getRange("E39").clear();
  shUserForm.getRange("E41").clear();
  shUserForm.getRange("E43").clear();
  shUserForm.getRange("E45").clear();
  shUserForm.getRange("E49").clear();
  shUserForm.getRange("E51").clear();
  shUserForm.getRange("E53").clear();

  valuesFound=true;

  return;
}
}

if(valuesFound==false)
{
  ui.alert("No RECORD FOUND!");

}
}

Upvotes: 3

Views: 145

Answers (2)

Tanaike
Tanaike

Reputation: 201523

Modification points:

  • If Reacod not found of it gives the message Reacod not found. is ui.alert("No RECORD FOUND!"); and if the value of var str = shUserForm.getRange("E13").getValue() is correct and also the column "A" of "SUPPORTING SHEET" sheet has the value of str, I thought that the reason for your issue is due to var rowValue = values&[i]; as mentioned by TheWizEd's comment. But from I have tried this by removing "&" but still not working properly, I'm worried that the column might be different from your expected column.

    • In this case, please check the value of var str = shUserForm.getRange("E13").getValue() again.

  • By the way, in your script, ui is not declared. Please be careful about this.

  • In your script, response of var response = ui.alert("Delete", 'Do you want to delete the record?',ui.ButtonSet.YES_NO); is not used. In this case, the script after this line is run for both "Yes" and "No".

  • In your script, I thought that process cost can be reduced a little from your showing script.

When these points are reflected in your script, how about the following modification?

Modified script:

function deleteRow() {
  var column = "A"; // Please set the column letter you want to search.

  var ui = SpreadsheetApp.getUi();
  var myGooglSheet = SpreadsheetApp.getActiveSpreadsheet();
  var shUserForm = myGooglSheet.getSheetByName("User Form");
  var datasheet = myGooglSheet.getSheetByName("SUPPORTING SHEET");
  var response = ui.alert("Delete", 'Do you want to delete the record?', ui.ButtonSet.YES_NO);
  if (response == ui.Button.NO) return;
  var str = shUserForm.getRange("E13").getValue();
  var ranges = datasheet.getRange(`${column}1:${column}${datasheet.getLastRow()}`).createTextFinder(str).matchEntireCell(true).findAll();
  if (ranges.length == 0) {
    ui.alert("No RECORD FOUND!");
    return
  };
  ranges.reverse().forEach(r => datasheet.deleteRow(r.getRow()));
  shUserForm.getRangeList(["E13", "E15", "E17", "E19", "E21", "E23", "E27", "E29", "E31", "E37", "E39", "E41", "E43", "E45", "E49", "E51", "E53"]).clear(); // or clearContent()
  SpreadsheetApp.flush();
  ui.alert("Record Deleted!");
}

  • When this script is run, a search value is retrieved from "E13" of "User Form" sheet. And, using the search value, the rows are searched using TextFinder. In this case, the script can set the search column with var column = "A";. When the search value is found, the row is deleted and the ranges ["E13", "E15", "E17", "E19", "E21", "E23", "E27", "E29", "E31", "E37", "E39", "E41", "E43", "E45", "E49", "E51", "E53"] is cleared.

  • If you want to change the search column, please modify var column = "A";.

References:

Upvotes: 1

Cooper
Cooper

Reputation: 64100

Try this:

function deleteRow() {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("User Form");
  var sh2 = ss.getSheetByName("SUPPORTING SHEET");
  var response = ui.alert("Delete", 'Do you want to delete the record?', ui.ButtonSet.YES_NO);
  var str = sh1.getRange("E13").getValue();
  var vs2 = sh2.getDataRange().getValues();
  const rgl = sh1.getRangeList(["E13","E15","E17","E19","E21"])l
  var valuesFound = false;
  let d = 0;
  for (var i = 0; i < vs2.length; i++) {
    var rowValue = vs2[i];
    if (rowValue[0] == str) {
      var iRow = i + 1;
      sh2.deleteRow(i + 1 - d++);
      ss.toast("Record Deleted!");
      rgl.getRanges().forEach(rg => rg.clearContent());
    }
  }
}

Upvotes: 1

Related Questions