CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
William Allred
William Allred

Reputation: 1

Google Sheets Script, Can't Remove Form Destination

I built a script that created a large number of forms. Some of them were duplicated, and I'm trying to remove those forms and their corresponding destination sheets. Here's what I've got:

function removeDups(){
 var ss, maint, sheets, form, destsheet, d1, d2, s1,s2
 ss = SpreadsheetApp.getActive();
 maint = ss.getSheetByName("Maintenance");
 destsheet = SpreadsheetApp.openByUrl(maint.getRange("B1").getValue());
 sheets = destsheet.getSheets();
 for (var i = 0; i<sheets.length; i++){
 s1 = sheets[i]
 s2 = sheets[i+1];
  d1 = s1.getRange("C1").getValue();
  d2 = s2.getRange("C1").getValue();
    if (d1.toString() == d2.toString()){
      form = FormApp.openByUrl(s2.getFormUrl())
      form.removeDestination();
      destsheet.deleteSheet(s2);
    }
   }
   }

It throws a "cannot delete sheet with linked form" error each time.

I've tried the following:

  1. Removed the deleteSheet line, with the intention of having script go back and delete sheets without linked forms. In this case the script completes, but forms remain linked.
  2. I've tried changing the destination for duplicate forms to another spreadsheet. In this case the form winds up linked to two sheets, and I still cannot delete the original linked sheet.

What am I missing here?

Edit: When I open the form itself, it is no longer linked to a spreadsheet. So the script removes the form destination as it should, but this is not reflected in the spreadsheet.

Upvotes: 0

Views: 307

Answers (2)

William Allred
William Allred

Reputation: 1

Thanks for the suggestions all. I failed to mention, but I had tried SpreadsheetApp.flush() previously, with no real improvement. I believe the issue was just too many sheets with embedded formulas in a single spreadsheet file, which led to this and other issues. I wound up dividing the form responses among 20(!) separate spreadsheets, with an onSubmit trigger installed for each of those. I also removed embedded formulas within the sheets, and let the trigger-run script do all of the work. Between those two everything is working smoothly now.

Upvotes: 0

Wicket
Wicket

Reputation: 38422

Add SpreadsheetApp.flush() before destsheet.deleteSheet(s2);

Related

Upvotes: 1

Related Questions