TypeError: Cannot read property 'Values' of undefined (line 37, file "Code") - Google Apps Scrit

Question

I've created a google apps script allowing me to create some Google Docs templates from a google sheets.

Here is the code :

function createDocument2 () {
  var headers,i,L,scriptProps,tactics;
  var endRowToRange,rangeForDate,sh,sheetTabName,ss,ssFileID,startRowToRange,templateId;
 
  ssFileID = 'Google Sheet File ID';
  templateId = 'Google Doc Template ID';
  sheetTabName = "Google Sheet Tab Name";
  
  ss = SpreadsheetApp.openById(ssFileID);
  sh = ss.getSheetByName(sheetTabName);
  endRowToRange = sh.getLastRow(); //The end row number will always be the last row in the sheet tab
  Logger.log(endRowToRange);
  
  scriptProps = PropertiesService.getScriptProperties();
  
  startRowToRange = scriptProps.getProperty('startRow'); // Where the start row begins for this run of the code  

  /* endRowToRange = endRowToRange.toString(); //Needs to be a string to concatenate the A1 notation */
  
  startRowToRange = startRowToRange.slice(0,startRowToRange.indexOf(".")); // Remove the decimal places FROM THE STRING
  /* endRowToRange = endRowToRange.slice(0,endRowToRange.indexOf(".")); // Remove the decimal places */
  Logger.log(endRowToRange);
  
  if (!startRowToRange) {
    startRowToRange = 2;
  }

  
  rangeForDate = 'A' + startRowToRange + ":I" + endRowToRange; // Build the A1 Notation for the data range


  Logger.log('rangeForDate' + rangeForDate);
  
  // Where we fill the Google Docs template

  
  headers = sh.Spreadsheets.Values.get(ssFileID,'A1:I1');
  tactics = sh.Spreadsheets.Values.get(ssFileID,rangeForDate);

  L = tactics.values.length;
  var i;
  
  for (i = 0; i < L; i ++) {

    Logger.log (tactics);

    var fpn = tactics.values[i][0];
    var nom = tactics.values[i][1];
    var cp = tactics.values[i][2];
    var tel = tactics.values[i][3];
    var email = tactics.values[i][4];
    var type = tactics.values[i][5];
    var prog = tactics.values[i][6];
    var date = tactics.values[i][7];
    var time = tactics.values[i][8];

    var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
    DriveApp.getFileById(documentId).setName(fpn + ' ' + nom.toUpperCase());

    var head = DocumentApp.openById(documentId).getHeader();
    head.replaceText ('##FP##', fpn);

    var body = DocumentApp.openById(documentId).getBody();
    body.replaceText ('##NOM##', nom);
    body.replaceText ('##CP##', cp);
    body.replaceText ('##EMAIL##', email);
    body.replaceText ('##TEL##', tel);
    body.replaceText ('##TYPE##', type);
    body.replaceText ('##PROG##', prog);
    body.replaceText ('##DATE##', date);
    body.replaceText ('##TIME##', time);

  }
  
  // Loop to retreive the values processed before
  
  scriptProps.setProperty('startRow',endRowToRange + 1); // Save new start value
}

I've duplicated this script to another folder and it's not working anymore, it's saying "TypeError: Cannot read property 'Values' of undefined (line 37, file "Code") - Google Apps Scrit".

When I run it from my initial folder, it's working perfectly.

Does anybody have an ideas about the problem ?

Thank you in advance. Matt

Answer 1

So, you need to make a few changes.

After

startRowToRange = scriptProps.getProperty('startRow');

add

if(endRowToRange<startRowToRange) return;

change headers and tactics to

headers = sh.getRange('A1:I1').getValues();
tactics = sh.getRange(rangeForDate).getValues();

instead of tactic.values use just tactics