CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
Amy Roach
Amy Roach

Reputation: 21

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

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".

Upvotes: 2

Views: 2388

Answers (2)

TheMaster
TheMaster

Reputation: 50443

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:

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

References:

Upvotes: 4

alexamy
alexamy

Reputation: 23

Now form urls are suffixed with /edit and /viewport, which you must remove to properly compare. I end up with this solution:

function connectCurrentSpreadsheet(form, sheetName) {
  const spreadsheet = SpreadsheetApp.getActive();
  form.setDestination(FormApp.DestinationType.SPREADSHEET, spreadsheet.getId());

  // rename spreadsheet
  const formUrl = form.getEditUrl().replace('/edit', '');
  spreadsheet.getSheets().forEach(sheet => {
    const destFormUrl = sheet.getFormUrl().replace('/viewform', '');
    if(destFormUrl === formUrl) {
      sheet.setName(sheetName);
    }
  });
}

Upvotes: 1

Related Questions