CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-formsgoogle-sheets-api
Maxunm
Maxunm

Reputation: 23

How do you use a script to delete a row off of a spreadsheet that is linked to a form?

I have this script that does several things with data once it is entered into a google form, but I need to make sue that when two entrants have the exact same name that it deletes the previous entry entirely.

function formChanger() {
  var doc = DocumentApp.openById('THIS WAS MY ID');
  var body = doc.getBody();
  var date = body.getListItems();
  var dates = [];
  for(var i = 0; i<date.length;i++)
  {
    dates.push(date[i].getText());
  }
  var form = FormApp.openById('THIS WAS MY ID');
  var items = form.getItems();
  var ss = SpreadsheetApp.openById("THIS WAS MY ID");
  Logger.log(ss.getName());
  var sheet = ss.getSheets()[0];
  var values = sheet.getSheetValues(2, 4, sheet.getLastRow() , 1);
  Logger.log(values);
  var names = sheet.getSheetValues(2, 2, sheet.getLastRow(), 1);
  var item = items[2].asMultipleChoiceItem();
  var choices = item.getChoices()
  for(var i=names.length; i>-1; i--){
    for(var j=names.length; j>-1; j--){
      if(names[i]==names[j] && i != j)
        sheet.deleteRow(i);
    }
  }
  var h = -1;
  var j = -1;
  var k = -1;
  var l = -1;
  for(var o = 0; o<values.length; o++){        
    if(choices[0].getValue().equals(values[o].toString()))
      h++;
    if(choices[1].getValue().equals(values[o].toString()))
      j++;
    if(choices[2].getValue().equals(values[o].toString()))
      k++;
    if(choices[3].getValue().equals(values[o].toString()))
      l++;
  }
  if(h>3)
    dates.splice(0,1);
  if(j>3)
    dates.splice(1, 1);
  if(k>3)
    dates.splice(2, 1);
  if(l>3)
    dates.splice(3, 1);
  emptyDocument();
  Logger.log(h);
  Logger.log(j);
  Logger.log(k);
  Logger.log(l);
  item.setChoices([
     item.createChoice(dates[0]),
     item.createChoice(dates[1]),
     item.createChoice(dates[2]),
     item.createChoice(dates[3])
 ]);
  for(var i = 0; i<dates.length; i++)
    body.appendListItem(dates[i]);
  Logger.log(doc.getName()+" Contains:");
  Logger.log(dates);
}

Yes the code is a mess, and I'm sure that it could be done a better way, but the important part is that I could be able to delete the line of information that is repeated. The compiler will not allow me to do this because the Spread Sheet is linked to the form. is there a way around this?

Upvotes: 1

Views: 908

Answers (1)

user3717023
user3717023

Reputation:

The following attempts at deletion are blocked in sheets receiving form data:

  • deletion of columns with form data
  • deletion of the row with form questions - that is, row 1

Other rows can be deleted at will. This behavior is exactly the same for scripts as it is for user actions.

Your script attempts to delete row 1 because it's buggy. I quote the relevant part:

  var names = sheet.getSheetValues(2, 2, sheet.getLastRow(), 1);
  for(var i=names.length; i>-1; i++){
    for(var j=names.length; j>-1; j++){
      if(names[i]==names[j] && i != j)
        sheet.deleteRow(i);

What row is names[i] in? It's in row i+2, because i=0 corresponds to row 2. Yet, you attempt to delete row numbered i, two rows above the intended one.

Besides, i>-1; i++ is absurd; you want i-- there.

Here is a simple script that deletes row with duplicates; it's tested with my form responses. It traverses the contents of "Form Responses 1" sheet from bottom to top; if two rows have the same value in column C, the older one gets deleted. I do take care not to attempt deletion of row 1.

(The reason to do this in bottom-up order is to avoid dealing with rows that moved up because others were deleted.) 

function deleteDupes() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Form Responses 1');
  var values = sheet.getDataRange().getValues();
  for (var i = values.length - 1; i > 1; i--) {
    if (values[i][2] == values[i-1][2]) {
      sheet.deleteRow(i);
    }
  }
}

Upvotes: 1

Related Questions