Google Apps Script Spreadsheet rename Error

Question

I am working on a Google Apps Script project and I have run into a problem with my last line of code:

sheets[0].setName(sheet.getRange(i,3).getValue());

The script takes Google Form input that targets to the spreadsheet "mysheet" and creates forms from that input. If column I (9) does not have text in it the script fires, builds a new form, fills in column 9, and a creates a new destination spreadsheet in the current workbook.

This all works well. The problem that I am having is the last stage, renaming the newly created spreadsheet that is created. The script either dies with no error or I receive a non-descriptive Service Code error. I basically need the newly generated spreadsheet to rename to the name of the form. I have another sheet that will do a vlookup and the name of the new sheet can't be a random "Form Responses #"

I found this bug in Google's code system. https://code.google.com/p/google-apps-script-issues/issues/detail?id=5537

This, to me anyway, describes what is happening. Early on in the project the bug was causing the sheet in position 1 to rename as the system thought it was 0. I am not sure why that is not happening anymore. I must have changed something but the script editor only goes back about a dozen or so revisions so I can't track it.

I thought if I forced a name reset on the sheet "mySheet":

sheet.setName("mySheet");

it might work. And it did. Since then I've added a ton more code (below is the skeleton version) and the work around no longer works. I've trimmed out my code back down again to see if it was something I added and it still doesn't seem to work. I've also tried setting a different sheet to be the active sheet and that did not seem to work either.

Does anyone have any suggestions? Thanks in advance for your thoughts.

function myFunction() {
  var ss = SpreadsheetApp.openById('1gg8FTt_3_Ude5qEmpqlvjZdQocXbYzOKJrgGeLC3cMc');
  var sheet = ss.getSheetByName("mySheet");
  var range = sheet.getRange("A1:I7");
  for (var i = 1; i <= range.getNumRows(); i++) {
      var cell = sheet.getRange(i,9).getValue();
      if(cell == ''){
        var form = FormApp.create(sheet.getRange(i,3).getValue());
        var linktoliveform = form.getPublishedUrl();
        var contentsforcell = '=HYPERLINK("' + linktoliveform + '","' + sheet.getRange(i,3).getValue() + '")';
        sheet.getRange(i,9).setValue(contentsforcell);
        form.setCollectEmail(true);
        form.setDescription(sheet.getRange(i,4).getValue());
        var item = form.addParagraphTextItem();
        item.setTitle(sheet.getRange(i,5).getValue());
        form.setDestination(FormApp.DestinationType.SPREADSHEET,'1gg8FTt_3_Ude5qEmpqlvjZdQocXbYzOKJrgGeLC3cMc');
        //sheet.setName("mySheet"); //reset name on mySheet sheet to force sheets/getsheets list to renumerate. https://code.google.com/p/google-apps-script-issues/issues/detail?id=5537
        var sheets = ss.getSheets();
        sheets[0].setName(sheet.getRange(i,3).getValue());            
        }
      }
  }

Have you checked that the value returned from i, 3? You are renaming the same sheet seven times, I can't tell why. — JSDBroughton
Hi Jonathon. Yes, i,3 correctly returns the name of the name of what the form is supposed to be called (Differentiation). It creates the form successfully in my drive. The loop runs across the whole sheet mysheet (I limited it to 7 cells for this post: it was A1:I originally) basically to scan what rows have not been processed yet. So if the first three rows in column I already have forms, they won't get new ones. — user3853379
Try SpreadsheetApp.flush() before the setName command, to make sure the sheet creation actually takes place before this command is executed. — user3717023
Hi Sandwich. That kind of worked. It would do the first one correctly and then the script would die with no error. It was also running really slow. This morning I created a new destination spreadsheet for the form and set the script up for the new spreadsheet. I was able to go back to my sheet.setname("mysheet") and remove the flush. It seems to be working properly again. My guess is something was corrupted or the sheet had become bloated from testing even though I was removing the created sheets between runs. The script runs much quicker now. Thanks for your help! — user3853379

user3853379 user3853379 · Accepted Answer · 2016-05-11T14:15:41

I set up a new destination sheet for the originating form and the script began working properly again with the

sheet.setName("mySheet");

back in play. My guess is something was corrupted or the sheet had become bloated from testing.

Google Apps Script Spreadsheet rename Error

1 Answers