How to change the sheet name for Google Form responses in Google Apps Script?

0
votes

Copies of a master Google Form are emailed to groups. When a member of each group completes the form, the response is sent to a sheet in a Google Spreadsheet. If there are 10 groups, there would be 10 sheets in the spreadsheet.

The name of each sheet would default to Form Responses 1, Form Responses 2, Form Responses 3... etc.

How to change the Form Response sheet name to the name of the group.

Here is what I have attempted.

 var form = FormApp.openById(copyForm.getId());
 var formLink = form.getPublishedUrl();
 var masterSpreadsheet = SpreadsheetApp.openById("ID");

 form.setDestination(FormApp.DestinationType.SPREADSHEET, masterSpreadsheet.getId());

 var sheets = masterSpreadsheet.getSheets(); 
 
 for(var i = 0; i < sheets.length; i++) {
    if(sheets[i].getFormUrl() == formLink) {
      sheets[i].setName("Group Name");
    }
  }

It's renaming the sheet before the newly added sheet in the spreadsheet.

Thanks in advance.

2
google-apps-scriptgoogle-sheetsgoogle-forms
Log the two urls and show how they look like. – TheMaster

2 Answers

2
votes

One broad way is to create a table of group names and spreadsheets URL then use it to rename the sheet.

One way to implement the above, could be to use an on form submit trigger to rename the sheet: if the sheet has the default name pattern, rename the sheet accordingly.

var table = [
  ['Group A', 'url1'],
  ['Group B', 'url2']
]

function respondToOnFormSubmit(e){
  var sheet = e.range.getSheet();
  var name = sheet.getName();
  if(/^Form Responses/.test(name)){
    var url = sheet.getFormUrl();
    var newName = table.filter((row) => return row[1] === url;)
    if(newName.length > 0){
      sheet.setName(newName[0]);
    }
  }

Related

1
votes

Here's how I worked around the issue:

//After your current setDestination line below:
form.setDestination(FormApp.DestinationType.SPREADSHEET, masterSpreadsheet.getId());

//insert a new sheet which will be at index [0]. This forces the sheet linked to the form created first at index [1].

// rename the sheet at index [1] -- your form response sheet
// delete the newly inserted sheet at index [0]