How to specify the spreadsheet tab name of a Google form destination using Apps Script

2
votes

I'm working with Google Script Editor and I've created a form using based on a Google Sheet. I've specified helptext for certain questions on the form based on the current cell selected from my sheet. Then I've set the destination for my form results into a different Google sheet. I want to specify the name of the tab based on that current cell from the current sheet. Is that possible? Here is what I have right now:

var DestinationSheet = SpreadsheetApp.openByUrl('URL here');
  form.setDestination(FormApp.DestinationType.SPREADSHEET, DestinationSheet.getId(););

But how do I specify the TAB name, as right now it just gives it a generic name with a new number each time, i.e., "Form Responses 5".

I thought I could include this somehow: DestinationSheet.insertSheet("currentcell"); But that just inserts yet another sheet named "currentcell".

1
google-apps-scriptgoogle-sheetsgoogle-forms

1 Answers

4
votes

But how do I specify the TAB name, as right now it just gives it a generic name with a new number each time, i.e., "Form Responses 5".

It's currently not possible to set the sheet name. But you can rename the "Form Responses 5" sheet.

Snippet:

var DestinationSheet = SpreadsheetApp.openByUrl('URL here');
  form.setDestination(FormApp.DestinationType.SPREADSHEET, DestinationSheet.getId(););
  const formUrl = form.getEditUrl();
  DestinationSheet
    .getSheets()
    .forEach(function(sheet){
       var sheetUrl = sheet.getFormUrl()
      //TODO Check format of both urls
      if (sheetUrl  === formUrl) 
        sheet.setName(DestinationSheet.getCurrentCell().getValue());//Function should be called from menu/button
    })

References: