I am trying to unlink, and delete a google form to google sheet link and then relink it to a new sheet with the same name

Question

Until I added the dummy sheet "none", the new created sheet was not being picked up during the foreach loop. However, if the deleted sheet was not the first subsheet in the Google Sheet, it worked fine also. Not sure why this was happening.

function clearForm() {
  var deleter = 0;
  var itemsToDelete = form.getItems();
  form.deleteAllResponses();
  form.setShowLinkToRespondAgain(false);
  form.removeDestination();
  itemsToDelete.forEach(function(next) {
    deleter++;
    form.deleteItem(next);
  });

  SpreadsheetApp.openById(currentSheetID).deleteSheet(currentSheet);

  form.setDestination(FormApp.DestinationType.SPREADSHEET, currentSheetID);

  mainSheet.insertSheet("None", 0);

  var ss = mainSheet.getSheets();

  var v = 1;
  ss.forEach(function(next) {
    Logger.log(v++);
    if (next.getName() != "Form Responses 2" && next.getName() !=
      "Form Responses 1" && next.getName() != "None") {
      next.setName(currentSheetName);

    }
  });

SpreadsheetApp.openById(currentSheetID).deleteSheet(mainSheet.getSheetByName(
    "None"));
}

question from:https://stackoverflow.com/questions/65927516/i-am-trying-to-unlink-and-delete-a-google-form-to-google-sheet-link-and-then-re

Answer 1

I understand that your goal is to take an existing Form/Sheet pair, delete the Sheet, create a new Sheet and join it to the original Form. If my understanding is correct, then you only need some small tweaks in your code to achieve your objective.

First thing is to change the old response Sheet for the new one. With Form.getDestinationId() you can get the ID of the old response Sheet (it will be useful later for cleaning up). Then you could use Form.setDestination() to make the change happen. If you didn't create the new response Sheet yet, you can use SpreadsheetApp.create() to do so.

If that ended fine, then you can send the old Sheet to the trash bin using DriveApp.getFileById().isTrashed() and finish this approach. Please, let me know if you have any questions about this process.