Google Scripts - Can't Find Destination Sheet After Form Creation

Question

My script creates a Google Form programmatically and sets the destination to the current Spreadsheet:

var sheetId = SpreadsheetApp.getActiveSpreadsheet().getId();  
var form = FormApp.create(acctName).setTitle(acctName).setDestination(FormApp.DestinationType.SPREADSHEET, sheetId);

// several form items added here

Next, I'd like to rename the destination Sheet and make a few formatting edits. When I do, however, the sheet doesn't seem to exist. For example, getSheets() does not contain the new Sheet! (And yes, ideally I'd like to open the sheet by ID; see function below which also doesn't work.)

var sheets = SpreadsheetApp.openById(form.getDestinationId()).getSheets();
SpreadsheetApp.setActiveSheet(sheets[0]);

The above code opens what I would consider index 1, not index 0, because the sheet isn't indexed. I even tried creating a few second delay (hoping that it was just a matter of time to allow the sync to happen between the client and server) but without any success.

I have also tried something like the following, as suggested elsewhere here on Stack Overflow, but the destination Sheet doesn't come up in getSheets() even when called through a separate function:

function getFormDestinationSheetId(form) {

  var destinationId = form.getDestinationId();      
  if(destinationId) {

    var spreadsheet = SpreadsheetApp.openById(destinationId);        
    spreadsheet.getSheets().forEach(          
      function(sheet) {            
        var sheetFormUrl = sheet.getFormUrl();
        if(sheetFormUrl) {             
          var sheetForm = FormApp.openByUrl(sheetFormUrl);
          if(sheetForm.getId() == form.getId()) {
            return sheet.getSheetId();
          }
        }
      }
    );
  }
  return null;
}

I haven't been able to find anyone have a similar problem on the webs. Any advice would be appreciated. Thanks!

I'm not familiar with this API, but wouldn't sheets[0] be the logical reference to the first sheet, since arrays are (usually) zero-based numbering? — Jeremy Caney
Aside, overall this is a well-written first post. Your problem is clear, your tags are appropriate, and you include the relevant code. Thank you for putting so much care and attention into that. Hopefully you're able to get a knowledgeable response from someone more familiar with Google's APIs than myself. — Jeremy Caney
Try adding SpreadsheetApp.flush() just after opening the spreadsheet. If you open the spreadsheet manually, do you see the destination sheet? — TheMaster
Jeremy ... sheets[0] returns what I would consider sheets[1]. And thank you for the feedback. — permafrost91

James D James D · Accepted Answer · 2020-03-22T21:27:05

Welcome to Stack!

I assume your script is bound to a sheet? Depending on how you're calling the script you may not see the new sheets because of browser caching.

function myFunction() {
  var sheet = SpreadsheetApp.getActive();
  var sheetId = sheet.getId();  
  var form = FormApp.create('acctName').setTitle('acctName').setDestination(FormApp.DestinationType.SPREADSHEET, sheetId);

  var ssSheets = sheet.getSheets();

  var respSheet = ssSheets[0]

  respSheet.getRange('A1').setBackground('RED');
  respSheet.getRange('C1').setFormula('=COUNTA($C$2:$C)');
  respSheet.setColumnWidth(2, 100);

  SpreadsheetApp.flush();
}

Google Scripts - Can't Find Destination Sheet After Form Creation

1 Answers