Mara
Mara

Reputation: 35

Why is my row appending twice in google sheets?

My script is set to copy rows from a form response sheet into different sheets depending on the type of item entered in the form. For some reason, it has started appending the row twice into the sheets(creating a duplicate - see screenshot below) with the exact same timestamp. It had not done this previously, and still only happens haphazardly, but often. Is the problem with my script or google sheets itself?

Spreadsheet Screenshot

 function onFormSubmit() {//Distributes Form Submissions to the appropriate sheets depending on format and audience whenever new submission is made

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var responseSheet = ss.getSheetByName("Item Request"); //points to form response sheet
  var lastSub = responseSheet.getLastRow(); //retreives last row (assumed last submission)

  //the following point to the cells within the last submission row
  var tsCell = responseSheet.getRange(lastSub,1);
  var lnCell = responseSheet.getRange(lastSub,2);
  var fnCell = responseSheet.getRange(lastSub,3);
  var lCardCell = responseSheet.getRange(lastSub,4);
  var phoneCell = responseSheet.getRange(lastSub,5);
  var audienceCell = responseSheet.getRange(lastSub,6);
  var formatCell = responseSheet.getRange(lastSub,7);
  var genreCell = responseSheet.getRange(lastSub,8);
  var titleCell = responseSheet.getRange(lastSub,9);
  var authorCell = responseSheet.getRange(lastSub,10);
  var isbnCell = responseSheet.getRange(lastSub,11);
  var pubCell = responseSheet.getRange(lastSub,12);
  var staffCell = responseSheet.getRange(lastSub,13);
  var notesCell = responseSheet.getRange(lastSub,14);

  //the following retreive values from those cells
  var tsVal = tsCell.getValue();
  var lnVal = lnCell.getValue();
  var fnVal = fnCell.getValue();
  var lCardVal = lCardCell.getValue();
  var phoneVal = phoneCell.getValue();
  var audienceVal = audienceCell.getValue();
  var formatVal = formatCell.getValue();
  var genreVal = genreCell.getValue();
  var titleVal = titleCell.getValue();
  var authorVal = authorCell.getValue();
  var isbnVal = isbnCell.getValue();
  var pubVal = pubCell.getValue();
  var staffVal = staffCell.getValue();
  var notesVal = notesCell.getValue();
  //***any other addtions will need to be reflected at the end of the if statement below

  //the following if statement determines which sheet the response values will be copied to
  //(see notes at the very bottom of the script for syntax help for future edits to the if statement)

  if (audienceVal == "Spanish" || formatVal == "e-Book" || formatVal == "e-Audio") {
    var selector = "Silvia";   
    var targetSheet = ss.getSheetByName(selector);
  }
  else if (audienceVal == "Juvenile" ) {
    var selector = "Hannah Juv.";       
    var targetSheet = ss.getSheetByName(selector);
  }
  else if (audienceVal == "Young Adult" ) {
    var selector = "Hannah YA";
    var targetSheet = ss.getSheetByName(selector);
  }
  else if (formatVal == "DVD" || formatVal =="Blu-Ray" ) {
    var selector = "Jacki DVDs";
    var targetSheet = ss.getSheetByName(selector);
  }
  else { 
    var selector = "Jacki";
    var targetSheet = ss.getSheetByName(selector);
  }//end if 
 //=============================APPEND ROW===================
 targetSheet.appendRow([tsVal,lnVal,fnVal,lCardVal,phoneVal,audienceVal,formatVal,genreVal,titleVal,authorVal,isbnVal,pubVal,staffVal,notesVal]); //***any aditions to the values above will need to be added here as well
  var selectorCell = responseSheet.getRange(lastSub,15);
  selectorCell.setValue(selector);  //sends selector back to response sheet for clerical use
}//end onFormSubmit

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();

  if(actVal == "p@ssword" && colEdit == 17) {
for(i = 1; 1< 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

Upvotes: 0

Views: 288

Answers (1)

user6637260
user6637260

Reputation:

When you want to delete rows try to loop from the bottom because when you delete rows like this make shift between rows count, maybe this will helps you

for(i = dataA.length; i >= 1; i--){
  if (dataA[i][0].toString() == tsVal1.toString()){
    responseSheet.deleteRow(i + 1);
    actSheet.deleteRow(actRow);
    break;
    }

Upvotes: 1

Related Questions