google apps script mailmerge not extracting arrayformula results

Question

I am a novice when it come to google scripting. I have found some code online that takes the information from the current row of a google sheet (derived from a form) and places it into a google doc (a bit like a mailmerge but just for the current row). I have adapted it to suit my needs.

Here is what happens. The user completes a form that is linked to a sheet. There are a number of calculations that takes place on the sheet using arrayformula to provide additional information for the doc. Whichever row the user is on he clicks a button I have created, the information from that row will transfer into a template doc.

This works fine for the top row (row 2 where row 1 is the header row) but when I try to run it for row 3 the only information from the sheet that transfers is the non arrayformula information ie the information entered into the form on not the calculated information!

Can anyone give me some insight as to why and if there is a solution?

Here is a copy of the code used:

var employee_ID = "";
var SOURCE_TEMPLATE = "1roWDiQ8NuMF06thK-bim0iwmX1_mbjvvcNIDAxfZZlI";
var employee_SPREADSHEET = "1sFWhuN_nSVEWvuPjWz2gKPdDSKw8DJBe8uoapItC0G4";
var TARGET_FOLDER = "1teL41C9NM0O5ma_2BUNe-MMX0rkSiA4h";


function getCurrentRow() {
  var currentRow = 
SpreadsheetApp.getActiveSheet().getActiveSelection().getRowIndex();
  return currentRow;
}


function getRowAsArray(sheet, row) {
  var dataRange = sheet.getRange(row, 1, 1, 99);
  var data = dataRange.getValues();
  var columns = [];

  for (i in data) {
    var row = data[i];

    Logger.log("Got row", row);

    for(var l=0; l<99; l++) {
        var col = row[l];

        if(!col) {
            break;
        }

        columns.push(col);
    }
  }

  return columns;
}


function createDuplicateDocument(sourceId, name) {
    var source = DriveApp.getFileById(sourceId);
    var targetFolder = DriveApp.getFolderById(TARGET_FOLDER);
    var newFile = source.makeCopy(name, targetFolder);

    return DocumentApp.openById(newFile.getId());
}


function generateEmployeeDatasheet() {

 var data = SpreadsheetApp.openById(employee_SPREADSHEET);

  if(!employee_ID) {
    employee_ID = getCurrentRow();

    if (employee_ID == 1) {
      var employee_ID = Browser.msgBox("You need your cursor to be on the 
row within which you are working ", Browser.Buttons.OK);
    }
  }


  var sheet = data.getSheets()[1];
  var columns = getRowAsArray(sheet, 1);

  Logger.log("Processing columns:" + columns);

  var row = getRowAsArray(sheet, employee_ID);  
  Logger.log("Processing data:" + row);

  var employeeName = row[2];
  var timeStamp = row[0];
  var employeeDate = row[51];

  var target = createDuplicateDocument(SOURCE_TEMPLATE, employeeName + " 
Final Pay Letter " + employeeDate);

  Logger.log("Created new document:" + target.getId());

  for(var i=0; i<columns.length; i++) {

    var body = target.getActiveSection();
    body.replaceText("<<Name>>",row[2]); //col C   
    body.replaceText("<<Final pay period>>",row[4]); //col E
    body.replaceText("<<Training details1>>",row[9]); //col J

  }

}

Update 04/10/18 It appears that my problem exists in the getRowsArray function and in particular the:

if(!col) {
        break;
    }

If there is a null entry in my spreadsheet data the function breaks and therefore the data further along the spreadsheet row is not captured.
Does anyone now how to change this so this function identifies the last column in the row in another way?

Welcome to Stack Overflow! Please visit the help center, take the tour to see what and How to Ask. Do some research, search for related topics on SO; if you get stuck, post a minimal reproducible example of your attempt, noting input and expected output. — mplungjan
@Tim Edit to include structure of your spreadsheet,screenshot of your spreadsheet, the array formula used or the actual spreadsheet. Complete code is fine, but provide minimal reproducible example. What does your logs say? — TheMaster

Tedinoz Tedinoz · Accepted Answer · 2018-10-08T10:23:13

This is my go-to utility to get the last row containing data in a given column. Two lines - works 100% of the time.

    var Avals = ss.getRange("A1:A").getValues();
    var Alast = Avals.filter(String).length;
    Logger.log("The last row of data is "+Alast);

Credit: "Determining the last row in a single column"

google apps script mailmerge not extracting arrayformula results

1 Answers