CODEWITHSUNDEEP
javascriptgoogle-apps-scriptapp-inventor
yehet
yehet

Reputation: 1

How to select and delete a row chosen from ListView (MIT App Inventor) in Google Sheets with script editor?

Currently, I'm making a medication management app. Now, I need to create a delete function. To delete the medication, the script will have to filter the username and medication name (because there might be multiple users with same medications). When both matches, then that row should be deleted from the Google Sheets.

Below is the data of added medication by different users:

link: https://docs.google.com/spreadsheets/d/1IEWSf7gp52GS0nM5I4WyPv-nj3ujSA4cXwUSko77Auw/edit?usp=sharing

Below is my Google Apps Script:

    function doGet(e) {
  return ManageSheet(e);
}
function doPost(e) {
  return ManageSheet(e);
}

function ManageSheet(e) {

  //READ ALL RECORDS
  if ( e.parameter.func == "ReadAll") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0]; 
    var rg = sh.getDataRange().getValues(); 
    var outString = '';
      for(var row=0 ; row<rg.length ; ++row){
        outString += rg[row].join(',') + '\n';  
      } 
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
  }

  //READ SINGLE RECORD
   else if ( e.parameter.func == "ReadRecord") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var rg = sh.getDataRange().getValues();
    var outString = '';
    outString += rg[parseInt(e.parameter.medication)].join(',');
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
  }
  
  //CREATE NEW RECORD  
  if (e.parameter.func == "Create") {
    
    var ss = SpreadsheetApp.getActive();
    
    var sh = ss.getSheets()[0];
    
    var data =[e.parameter.username, e.parameter.medication, e.parameter.color, e.parameter.frequency, e.parameter.dosage, e.parameter.time,
    e.parameter.reminder1, e.parameter.reminder2,e.parameter.reminder3,e.parameter.reminder4,e.parameter.reminder5, e.parameter.remark]; 
    
    sh.appendRow(data);
    
    return ContentService.createTextOutput("Success");
  
  }

}

Below is my MIT App Inventor blocks for this screen:

My Pills screen

MIT App Inventor blocks for My Pills screen

Currently, the flow is as user enters the MyPills page, the start value will be the username. Then, the ListView will show all medications added by the user. To delete, after selecting from the list, the user can click the "Delete Medication" button and a confirmation notification will pop out with Yes and No selections. So, now I'm having trouble doing the delete function in scripting: Take the username and medication name as input and then filter the row that matches then delete that row. Please help, thank you so much!!!

Upvotes: 0

Views: 685

Answers (1)

Metric Rat
Metric Rat

Reputation: 316

You do not appear to have a delete function in your script....

Try something like this:

    // deletes a single record (and its row) from sheet. Requires row index and col1 to match
  else if ( fn == 'DELETE' ) {
    var index = e.parameter.INDEX;  //index in list
    var col1 = e.parameter.COL1;  // current/existing value of col1 - it could be replaced...
    for (var i = 0; i < rg.length; i++ ) {
      if ( index != undefined && i == index && col1 == rg[i][0] ) {
        sh.deleteRow(parseInt(index)+1);
      } 
    }
    return ContentService.createTextOutput("Existing record deleted");    
   }

Expects both the row index and the data in column 1 of the spreadsheet to match in order to delete. Adjust according to your needs.

Also see:

https://ai2.metricrat.co.uk/guides/google-sheet-crudq-ii

Upvotes: 0

Related Questions