0

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

11
  • 2
    Have your tried changing to headers = sh.getRange('A1:I1').getValues(); tactics =sh.getRange(rangeForDate).getValues(); Commented May 6, 2020 at 5:01
  • Hi @Ghost, thank you for your answer. I tried but it's not working too. I do not have the error anymore but it's not creating my template. Should I change the values below also ?Thanks in advance for your help. Commented May 6, 2020 at 9:26
  • Hi @matthiew Its hard to look at this way. I really need to look at your spreadsheet and see why its not working. Maybe if you can post link to your sample sheet and describe what you want to achieve, I can help Commented May 7, 2020 at 10:33
  • Hi @Ghost, thank you for your answer. I'll try to explain you easily. I have a landing page that receive some leads. All the leads are stored into a google sheets automatically. I have 3 landing pages running, so on my google sheet file, I have 3 tabs eg: LP 1, LP 2, LP 3. Once the datas are stored in the google sheet, the script shared above is creating some prospect pages with the customer datas automatically. The script find the new datas added and then create the google doc prospect page. Commented May 7, 2020 at 11:28
  • @Ghost I have 3 folders eg : LP 1, LP 2, LP 3 all containing the google doc template and the script so the prospect pages can be stored and we can know from which landing page the prospect come. On the folder LP 1, when I run the script, it's working. On the LP 2 and LP 3 folder, the script is doing the error mentioned above. Commented May 7, 2020 at 11:29

1 Answer 1

2

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.