How do I pull a Row from an Array in Google apps script Google sheets

Question

My spreadsheet is composed of a main sheet that is populated using a form plus several other sheets for the people who work with the responses submitted through the form. A script delegates the form responses to these other sheets depending on the type of item described in the response. The problem is, when Person A deletes an item from their respective sheet, it doesn't delete in the main sheet. My idea is that when you type a set password into the corresponding cell in row 'Q' in Person A's sheet, it matches the item by timestamp to the original form submission and deletes both the version of the item in Person A's sheet as well as the main sheet. However, I can't figure out what to set the range to to get it to point to the row in the array. Everything I have tried has sent back "undefined" in the debugger and won't delete anything. I think the problem is that I don't know how to get the row from the array that I have made. See my code below:

   function onEdit() {//copies edited items from individual selector sheets back onto main spreadsheet

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var actSheet = ss.getActiveSheet();
    var responseSheet = ss.getSheetByName("Item Request");
    var actCell = actSheet.getActiveCell();
    var actRow = actCell.getRow();
    var actVal = actCell.getValue();
    var actLoc = actCell.getA1Notation();
    var last = actSheet.getLastRow();
    var respLast = responseSheet.getLastRow();
    var dataA = responseSheet.getRange(1, 1, respLast, 1).getValues(); //compiles an array of data found in column A through last row in response sheet
    var tstamp1 = actSheet.getRange(actCell.getRow(), 1);
    var tsVal1 = tstamp1.getValue();
    var colEdit = actCell.getColumn();

    //===========THIS IS WHERE I'M STUCK=======================
 if ((actVal == "p@ssword") && (colEdit == 17)) {
      for (i = 1; i < dataA.length; i++) {
       if (dataA[i][0].toString == tsVal1.toString()) { 
         responseSheet.deleteRow(i + 1);
         actSheet.deleteRow(actRow);
         break;
       }
      }
    }

       else if (colEdit == 15) { //checks the array to see if the edit was made to the "O" column

            for (i = 1; i < dataA.length; i++) {//checking for timestamp match and copies entry 
                if (dataA[i][0].toString() == tsVal1.toString()) {
                    var toEdit = responseSheet.getRange(i + 1, 16);
                    toEdit.setValue(actVal);
                }
            }
       } 

     else if (colEdit == 16) { // checks the array to see if the edit was made in the "P" column

         for (i = 1; i < dataA.length; i++) {//checking for timestamp match and copies entry
             if (dataA[i][0].toString() == tsVal1.toString()) {
                var toEdit = responseSheet.getRange(i + 1, 17);
                toEdit.setValue(actVal);
             }
         }
     }  

    else {return;}
}//end onEdit

Cooper Cooper · Accepted Answer · 2017-08-25T03:26:14

I don't believe these are proper commands delRow.deleteRow();actCell.deleteRow(); Take a look at the documentation;

Okay I rewrote that function for you a bit but I'm stilling wondering about a couple of lines.

function onEdit(e) 
{
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var actSheet = ss.getActiveSheet();
    var responseSheet = ss.getSheetByName("Item Request");
    var actCell = actSheet.getActiveCell();
    var actRow = actCell.getRow();
    var actVal = actCell.getValue();
    var colEdit = actCell.getColumn();
    var respLast = responseSheet.getLastRow();
    var dataA = responseSheet.getRange(1, 1, respLast, 1).getValues(); 
    var tstamp1 = actSheet.getRange(actRow, 1);
    var tsVal1 = tstamp1.getValue();

    for(var i=0;i<dataA.length;i++)
    {
      if(new Date(dataA[i][0]).valueOf()==new Date(tsVal1).valueOf()) 
      { 
        if (actVal=="p@ssword" && colEdit==17) 
        {
          responseSheet.deleteRow(i + 1);
          actSheet.deleteRow(actRow);     
        }
        else if(colEdit==15) 
        { 
          var toEdit = responseSheet.getRange(i + 1, 16);//?
          toEdit.setValue(actVal);//?
        } 
        else if (colEdit == 16) 
        {
          var toEdit = responseSheet.getRange(i + 1, 17);//?
          toEdit.setValue(actVal);//?
        }  
      }
    }  
}

Can you explain the function of the lines with question marked comments?

How do I pull a Row from an Array in Google apps script Google sheets

1 Answers