CODEWITHSUNDEEP
google-apps-script
user3853379
user3853379

Reputation: 21

Google Apps Script Spreadsheet rename Error

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());            
        }
      }
  }

Upvotes: 1

Views: 794

Answers (1)

user3853379
user3853379

Reputation: 21

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.

Upvotes: 0

Related Questions