Google-apps-script – not working form submit Event Object

google docsgoogle-apps-scriptgoogle-forms

I have a Google form which I use to populate a Google docs from a certain template whenever a new response is submitted. In order to achieve this, I have written this script, but it is not working and showing this error TypeError: Cannot read property 'values' of undefined (line 3, file "Code"):

function createdocs(e) {

  var Contracttype = e.values[1];
  var Partnername = e.values[2];
  var Bankname = e.values[3];
  var Accountnumber = e.values[4];
  var TIN = e.values[5];
  var Bankcode = e.values[6];
  var Businesscode = e.values[7];
  var Phone = e.values[8];
  var Director = e.values[9];
  var Contractstartingdate = e.values[10];
  var Contractendingdate = e.values[11];
  var EmailAddress = e.values[12];

  var file = DriveApp.getFileById('10lZQhmbocTOmvGwklYnHNvXbdGXDmpbgFbvcnu67G7c'); 
  var folder = DriveApp.getFolderById('1O6QJZ5gRRoSUeolcVvKlMe65Wdb4rz9z')
  var copy = file.makeCopy(Partnername + ',' + Contractstartingdate, folder); 
  var doc = DocumentApp.openById(copy.getId()); 
  var body = doc.getBody(); 

  body.replaceText('{{Partner name}}', Partnername);  
  body.replaceText('{{Bank name}}', Bankname);
  body.replaceText('{{Account number}}', Accountnumber);
  body.replaceText('{{TIN}}', TIN);
  body.replaceText('{{Bank code}}', Bankcode);
  body.replaceText('{{Business code}}', Businesscode);
  body.replaceText('{{Phone}}', Phone);
  body.replaceText('{{Director}}', Director);
  body.replaceText('{{Contract starting date}}', Contractstartingdate);
  body.replaceText('{{Contract ending date}}', Contractendingdate);

  doc.saveAndClose(); 
}

I have used a youtube tutorial for this purpose and followed every step, But I am failing, how to define values in the script, I have installed the onformSubmit trigger. But there is something going wrong Please help me with this out

Best Answer

You are having this problem because you are running the script from the Script Editor. When you do this, the event objects aren't created - this is why "values" is described as "undefined".

You are using an onformsubmit trigger and event objects are created ONLY when a form is submitted.

Your spreadsheet must contain the Response sheet for a form. When a form is submitted, the Response sheet is automatically updated and the script is triggered. At this point, the event objects (includes "values") can be accessed.

References

Google Apps Script – Loop Through All Pages Except the First

First, the error: shs is an array, since you populate it with ss.getSheets(), so it doesn't have a method getActiveCell(). However, the elements of the array has that method.

You are also referencing a variable sheet, which you haven't defined anywhere.

Currently, your code is more suitable for going through a single sheet, you need to iterate through the shs array to have it operate on all sheets.

The following function will go through all cells in all sheets:

function onEdit(event){  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getSheets(), num = shs.length;
  var sheetName = [] 

  for (var sheetIdx = 0; sheetIdx < shs.length; sheetIdx++) {
      var sheet = shs[sheetIdx];

      for(var i = 1; i<num; i++) {
      ...
   }
}

But you wanted to skip the first sheet, so let's start with element 1 in the array (since the array is 0-based, element 1 is the second sheet):

function onEdit(event){  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getSheets(), num = shs.length;
  var sheetName = [] 

  for (var sheetIdx = 1; sheetIdx < shs.length; sheetIdx++) {
      var sheet = shs[sheetIdx];

      for(var i = 1; i<num; i++) {
        var editedCell = shs.getActiveCell();
        var columnToSortBy = 1;
        var tableRange = "A2:G99"; // What to sort.

        if(editedCell.getColumn() == columnToSortBy){   
          var range = sheet.getRange(tableRange);
          range.sort( { column : columnToSortBy, ascending: false });
        }
      }

   }
}

As for your comment, your code

function onEdit(event) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    for (var n in ss.getSheets()) {
        var sheet = ss.getSheets()[n];
        var name = sheet.getName();
        if (name == 'Dashboard') 
           return;
        var editedCell = ss.getActiveCell();
        var columnToSortBy = 1;
        var tableRange = "A2:G99";
        if (editedCell.getColumn() == columnToSortBy) {
            var range = sheet.getRange(tableRange);
            range.sort({column: columnToSortBy, ascending: false});
        }
    }
}

could work, if you replace return with continue. You don't want return, it ends the script. continue skips to the next element in the for loop.

Best Answer

Related Solutions

Google-apps-script – How to get the set values and formulas to populate to a row of values received from a Google Form

References

Google Apps Script – Loop Through All Pages Except the First

Related Topic