0

I'm using a Google Sheet to populate drop downs in a Google Form. There's not a lot of info to be transferred as such, but it's repeated 76 times...

I'll paint the picture first, so you have an idea of what I was/am looking for.

People will be working in groups of 5 or 8, and I want them to fill in a peer evaluation. I want them to be able to select a name from a drop down menu. Everyone has a position in a group, with a specific position title.

I don't want people evaluating themselves, and Google Forms doesn't allow any "in game" dynamics, so I made separate sections for each possibility. There are 12 possibilities, resulting in 77 drop down menus. (76 in total (5*4+8*7) + the person filling in the Form needs to fill in his own name as well).

So 77 questions which all require the same drop down menu, and 1 extra question which needs a different drop down menu, also from the same Sheet. So the Sheet has two columns on the left, and then 76 more columns, all loaded with =if($B2="","",$B2). I expect there will be some 75 rows of data in column B (and thus columns C through BZ). Column A will have significantly less rows with data.

The Form itself has become quite unwieldy as well, thanks to all the sections. That might not help the script process...

End of picture paint job.

I tried actually copying the data from column B to columns C through BZ, but that still resulted in the time exceeding error.

I'm a noob, so I found this code by searching the internet, and copied the formula seen in this guide (https://www.youtube.com/watch?v=o3AL7ASI_cA). The formula works; I tested it when my Form wasn't too extensive yet. This is the formula:

var ssID = "1mXy-SOblablablaGBUwLw0cU";
var formIDPE = "15-gIx4kblablablar9T_DRvg0";

var wsData = SpreadsheetApp.openById(ssID).getSheetByName("Games");
var formPE = FormApp.openById(formIDPE);


function main(){

 var labels = wsData.getRange(1, 1,1,wsData.getLastColumn()).getValues()[0]; 

  labels.forEach(function(label,i){
    var options = wsData
                  .getRange(2, i + 1,wsData.getLastRow()-1,1)
                  .getValues()
                  .map(function(o){ return o[0] })
                  .filter(function(o){ return o !== "" });

    updateDropDownUsingTitle1(label,options);
      });

}



function updateDropDownUsingTitle1(title,values) {

  var items = formPE.getItems();
  var titles = items.map(function(item){
    return item.getTitle();
  });

  var pos = titles.indexOf(title);
  if(pos !== -1){
    var item = items[pos];
    var itemID = item.getId();
    updateDropdown1(itemID,values);
  }
}

I don't really have an idea what I'm doing, so I'm unable to execute tips like this (https://ctrlq.org/code/20016-maximum-execution-time-limit) and this (https://stackoverflow.com/questions/7854573/exceeded-maximum-execution-time-in-google-apps-script).

Since the script goes down all columns, I assume it's possible to make it take a break after a certain column? And then later resume with the next column? Or the same deal with the Form questions...?

Or did I take an entirely wrong approach?

Thanks for your help in any case!

--
Rewatching the video I got the code from, I assume the solution would be somewhere in this line (possibly modifying wsData.getLastColumn())?

var labels = wsData.getRange(1, 1,1,wsData.getLastColumn()).getValues()[0]; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.