B.McKay
B.McKay

Reputation: 29

Google Form Script Population

I am trying to populate a google form with questions scraped from a google sheet. Currently when I run my code I am getting the questions created, but only 25% or so actually have the string, the rest are simply blank. The questions that appear correctly change every time I run the script. It is seemingly random.

function formPopulation() { 
  var ss = SpreadsheetApp.openById("--");
  var sheet = ss.getSheetByName('Tracker');
  var auditTool = ss.getSheetByName('Audit Tool');
  var validatorInfo = ss.getSheetByName('Validator Info');
  //Sheet Info

  var rows = auditTool.getLastRow(); //Number of Rows
  var columns = auditTool.getLastColumn(); //Number of Columns

  var startRow = 1; 
  var startColumn = 1; 


  var dataRange = auditTool.getRange(startRow, startColumn, rows, columns);  
  //getRange(first row of data, first column of data, last row of data, last column of data)

  var data = dataRange.getValues();
  //Sets working range of script

  var form = FormApp.openById("--");
  var item = form.addListItem();

  var entityName = "";
  var arrayOfEntities = [];
  var newEntity = '';

  for (var i = 4; i < columns; i++) {
     //4 because that is where entity names begin 
     entityName = data[i][2];
     Logger.log('entityName: ' + entityName);
     newItem = item.createChoice(entityName);
     arrayOfEntities.push(newItem);
  };

  item.setTitle("Select Entity").setChoices(arrayOfEntities);

  var requirement = "";
  var arrayOfRequirements = [];
  var newRequirement = '';

  for (var j = 5; j < rows; j++) {
     //5 because that is where Requirements begin
     if (data[0][j] != null)  {
     requirement = data[0][j];
           if (requirement != "" || requirment != null){
             requirement = "question #" + j; 
             Logger.log('requirement: ' + requirement);
             form.addMultipleChoiceItem().setTitle(requirement).setChoiceValues(['Complete', 'Incomplete']);  
      };
     };
   };
};

The first question is supposed to be a multiple choice item where each 'entity' is an option. The remainder of the questions are supposed to be whether each 'requirement' is marked complete or incomplete. Here is the spreadsheet I am working from

Upvotes: 2

Views: 186

Answers (2)

YNK
YNK

Reputation: 129

  • Here in last forloop

    requirement = "question #" + j;

  • Please verify, is it ok ? or you should use

    requirement = "question #" + j + ' ' +data[0][j];

Upvotes: 0

delagroove
delagroove

Reputation: 277

you have a typo: if (requirement != "" || requirment != null){

should be 'requirement'

Upvotes: 1

Related Questions