Making a for-loop look through rows

Question

I'm trying to make a script that replaces information in a template document (a contract). I've had it working looking through the columns of a google spreadsheet, but when trying to rewrite it to make it look through the rows of the spreadsheet instead of the columns it does not seem to work. It only returns information contained in the first row. I suspect it might have to do with the for-loop.

Here's a snippet of the code:

function lagkontrakt() {
  

  const docFile = DriveApp.getFileById("DOC_FILE_ID");
  const tempFolder = DriveApp.getFolderById("TEMP_FOLDER_ID");
  const pdfFolder = DriveApp.getFolderById("PDF_FOLDER_ID");
 
  var date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
  
  //copies the template//
  let copyFile = DriveApp.getFileById("FILE_ID").makeCopy(tempFolder);

  let copyId = copyFile.getId();
  let copyDoc = DocumentApp.openById(copyId);
  
  //fetches the content of the template//
  let copyBody = copyDoc.getBody();
  let copyHeader = copyDoc.getHeader();
  
  //defines active sheet//
  let activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SHEETNAME");
  
  let numOfRow = activeSheet.getLastRow();
 
  //getRange(row, column, numRows, numColumns)//
  let activeCol = activeSheet.getRange(1,2,numOfRow,1).getValues();
  let headerCol = activeSheet.getRange(1,1,numOfRow,1).getValues();
 
  let rowIndex = 0
  
  //search loop//
  for (; rowIndex < headerCol[0].length; rowIndex++){
  
    copyBody.replaceText('{' + headerCol[rowIndex][0] + '}', activeCol[rowIndex][0]);
    copyBody.replaceText("{dato}", date);

    copyHeader.replaceText('{' + headerCol[rowIndex][0] + '}', activeCol[rowIndex][0]);
    
  }
  
  copyDoc.saveAndClose();

}

This is the column-version of the same script, that works just fine:

let numOfCol = activeSheet.getLastColumn();
  
  let activeRow = activeSheet.getRange(2,1,1,numOfCol).getValues();
  let headerRow = activeSheet.getRange(1,1,1,numOfCol).getValues();
  

  let columnIndex = 0
  

  for (; columnIndex < headerRow[0].length; columnIndex++){
  
    copyBody.replaceText('{' + headerRow[0][columnIndex] + '}', activeRow[0][columnIndex]);
    copyBody.replaceText('{dato}', date);

    copyHeader.replaceText('{' + headerRow[0][columnIndex] + '}', activeRow[0][columnIndex]);
    
  }

Welcome to Stack Overflow. What do you mean by "it doesn't look to work"? — Rubén
The script only returns values for the first row of the spreadsheet. — Selato

Rob Rob · Accepted Answer · 2020-08-30T02:43:50

It looks like you have the variable in column A and the value in column B. Your For loop currently is limiting the rows that it searches to the length of the first row, which is to say that it will always be less than 1.

You need to change:

for (; rowIndex < headerCol[0].length; rowIndex++){

To:

for (; rowIndex < headerCol.length; rowIndex++){

If I'm not correct about how your data is laid out then you have reversed the array reference in a couple places and got an entire column instead of the entire row.

Making a for-loop look through rows

1 Answers