Creating Google Docs templates from Google sheets datas - need a way to avoid duplicate templates

[Matthieu Verneau]

Good morning all,

I created a google app script which allows me to duplicate google docs templates using data from a google sheets file. Everything works perfectly, however I have a little problem.

The script always creates the google docs templates from the first line of the google sheets document.

For example, if the script was launched and processed 100 lines of google sheets, on the second use, it will recreate the first 100 templates. However, I would like the script to create only the templates for the new lines added to the google sheets.

I do not know if my request is clear but here is the script below:

function createDocument () {

var headers = Sheets.Spreadsheets.Values.get ('1tkcP1lA8QKIuAnur7WnGrHiVI1hrXdzRZPwp3ruSjrI', 'A1: I1');

var tactics = Sheets.Spreadsheets.Values.get ('1tkcP1lA8QKIuAnur7WnGrHiVI1hrXdzRZPwp3ruSjrI', 'A2: I1000');

var templateId = '170TkGYOQCegZWe5ude1ODPewLeyE1HsxasONhsIDY4I';

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

Logger.log (tactics);

var fpn = tactics.values ​​[i] [0];

var name = 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 ('N °' + fpn + '' + name);

var head = DocumentApp.openById (documentId) .getHeader ();

head.replaceText ('## FP ##', fpn);

var body = DocumentApp.openById (documentId) .getBody ();

body.replaceText ('## NAME ##', name);

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);

}

}

Hoping someone can help me.

Regards,

Matthew

[Alan Wells]

You could store the row number of the last row processed in Properties Service.  If your Apps Script project is bound to the Sheet, I would use Script Properties which is a sub class of Properties Service.  The script properties values can be seen by going to "File" "Project Properties" in the script editor.

Another way people deal with this situation is to put a value in a cell at the end of the row that states "Done" or "Complete" and check for whether that row was processed.  That way, you can easily look at the row, and know what the status is.  Or you could copy rows to another sheet, and as they are processed, delete them. 

There are probably other ways to configure this.  You could store the value of the last row processed in a cell instead of Script Properties.  You could turn the processed rows a different color.  There are probably other ways that people could think of.

What do you think would work for you?

[Matthieu Verneau]

Hi, thank you so much for your really good answer!

Yes the script is bound to the sheet at the beginning of the script. I think any of the solution will work for me, which one is the easier to do ?

May I ask you if you have an idea of the code that I should add to my script ? I'm really novice on App Script and I don't have much clues about how to do it with your idea...

Your help will be really much appreciated.

I do like the way with the colors though.

Please keep me informed and thank you again for your kind assistance.

Matthieu

[Alan Wells]

The easiest way is to hard code the row start and end values, and then change them the next time that you run the code.  Note that I've remove the file ID's so if you copy and paste the code make sure to replace the file IDs.

function createDocument () {
  var headers,tactics;
  var endRowToRange,rangeForDate,startRowToRange;
 
  startRowToRange = 2;//Where the start row begins for this run of the code
  endRowToRange = 1000;//Hard code the end row
 
  rangeForDate = 'A' + startRowToRange + ":I" + endRowToRange;//Build the A1 Notation for the data range

  Logger.log('rangeForDate ' + rangeForDate);
 
  headers = Sheets.Spreadsheets.Values.get ('file_ID', 'A1: I1');
  tactics = Sheets.Spreadsheets.Values.get ('file_ID', rangeForDate);

  var templateId = 'file_ID';

[Matthieu Verneau]

Hi !

First of all, thank you so much for your time and help. I've tried your code and it's working perfectly.

My last question, if I may, is that the app script will be run automatically using time triggers.

On this line :

startRowToRange = 2;//Where the start row begins for this run of the code

Is it possible to change the value "2" by a google sheet variable (eg. NEW ROW ADDED)

So, when the script is running automatically, it can straight away know which new row are added.

Thank you a lot and more for your help.

Matt

[Alan Wells]

Try this:

function createDocument () {
  var headers,incrementBy,scriptProps,tactics;
  var endRowToRange,rangeForDate,startRowToRange;
 
  scriptProps = PropertiesService.getScriptProperties();
  incrementBy  = 1000;
 
  startRowToRange = scriptProps.getProperty('startRow');//Where the start row begins for this run of the code
  endRowToRange = scriptProps.getProperty('endRow');;//Retrieve the end row
 
  if (!startRowToRange) {
    startRowToRange = 2;
    endRowToRange = 1000;
  }
 
  rangeForDate = 'A' + startRowToRange.toString() + ":I" + endRowToRange.toString();//Build the A1 Notation for the data range

  Logger.log('rangeForDate ' + rangeForDate);
 
  //All the code in the middle here
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  scriptProps.setProperty('startRow',endRowToRange + 1);//Save new start value
  scriptProps.setProperty('endRow',endRowToRange + incrementBy);//Save a new value
  endRowToRange = scriptProps.getProperty('endRow');;//Hard code the end row
 
}

 

[Matthieu Verneau]

Hi,

Thank you for this new version.

I've changed the values of the code as follow :

function createDocument () {

  var headers,incrementBy,scriptProps,tactics;

  var endRowToRange,rangeForDate,startRowToRange;

 

  scriptProps = PropertiesService.getScriptProperties();

  incrementBy  = 1000;

  

  startRowToRange = scriptProps.getProperty('startRow'); // Where the start row begins for this run of the code

  endRowToRange = scriptProps.getProperty('endRow'); // Retrieve the end row

 

  if (!startRowToRange) {

    startRowToRange = 2;

    endRowToRange = 13;

  }

  

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

  Logger.log('rangeForDate' + rangeForDate);

  

  // Where we fill the Google Docs template

  

  headers = Sheets.Spreadsheets.Values.get('1tkcP1lA8QKIuAnur7WnGrHiVI1hrXdzRZPwp3ruSjrI','A1:I1');

  tactics = Sheets.Spreadsheets.Values.get('1tkcP1lA8QKIuAnur7WnGrHiVI1hrXdzRZPwp3ruSjrI',rangeForDate);

  var templateId = '1uOJ_r2nqfgS8tE-__-CvJP7nyObPLbmqpwQaN2iq8QA';

  for (var i = 0; i <tactics.values.length; 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

  scriptProps.setProperty('endRow',endRowToRange + incrementBy); // Save a new value

  endRowToRange = scriptProps.getProperty('endRow'); // Hard code the end row

  

}

It's working when I run it the first time, when I run it the second time, the script properties startRow and endRow are with decimal (eg: startRow = 14.0, endRow = 1013.0). It think that I should round the value rangeForDate. I tried with rangeForDate.toFixed(0); but it's not working.

tactics = Sheets.Spreadsheets.Values.get('1tkcP1lA8QKIuAnur7WnGrHiVI1hrXdzRZPwp3ruSjrI',rangeForDate);

So, when I change the script properties startRow and endRow manually and removing the .0 decimal, the second run of the script is working.

During the 3rd run of the script, the values startRow and endRow and completely wrong, it shows : startRow = 10131 and endRow = 10131000. It should be (if only one more row is added to the google sheet). startRow = 15 and endRow = 2013.

I'm trying to fix it since this morning, but couldn't make it works...

I'm really sorry to need your help again, but I'm sure that we are close to the good result.

Thank you in advance for your help.

Regards,

Matt

[Alan Wells]

Try this.  Make sure to copy the entire code.  I've remove the part to save the end number.  All you need to save is the new start number.

function createDocument () {
  var headers,i,incrementBy,L,scriptProps,tactics;

  var endRowToRange,rangeForDate,startRowToRange;
 
  scriptProps = PropertiesService.getScriptProperties();
  incrementBy  = 1000;
 
  startRowToRange = scriptProps.getProperty('startRow'); // Where the start row begins for this run of the code

  endRowToRange = Number(startRowToRange) + incrementBy;
  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

 
  if (!startRowToRange) {
    startRowToRange = 2;
    endRowToRange = 13;
  }

 
  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 = Sheets.Spreadsheets.Values.get('1tkcP1lA8QKIuAnur7WnGrHiVI1hrXdzRZPwp3ruSjrI','A1:I1');
  tactics = Sheets.Spreadsheets.Values.get('1tkcP1lA8QKIuAnur7WnGrHiVI1hrXdzRZPwp3ruSjrI',rangeForDate);

  var templateId = '1uOJ_r2nqfgS8tE-__-CvJP7nyObPLbmqpwQaN2iq8QA';

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

[Matthieu Verneau]

Hi,

Thank you again for this new version. It works good at the first run, but at the second run, the startRow value is 1001. But it should be 19 as there were 18 row processed before. 

Is there anyway to store the previous startRow values?

Thank you again and again.

Matt

[Alan Wells]

Okay, I think I misunderstood what you needed.  The code before saved the start row, so you can use that.

It seems that you are incrementing the start row and end row differently?  Is that the case?

Start row session one - 18

End row session one - 1000

Start Row session two - 19

End Row session two - 1001

Is the above correct, or something different?

[Matthieu Verneau]

Hi,

This case is correct but I'll try to explain my problem differently.

The google sheet linked to the code is used to store some leads from a landing page.

At the moment, we have 18 values on the google sheet (please see screenshot attached).

But, once people will submit there datas to the landing page, we will have more rows created on the google sheet. It could be 1 rows added or more.

At the moment we have 18 rows with datas, but it could be in 1 hour 28 rows with datas.

It means that if the first run created the 18 first templates, it should, on the second run, start from row 19 and end on row 28.

The next run will then start at row 29 et process the new rows added again and again.

The start row value is a known number only during the first run.

I don't know if my explanation is easy to understand.

Thank you for all,

Matt

[Alan Wells]

Okay, the code doesn't need an increment number.  The last row will always be the last row in the sheet tab.  So, you need to get the number of the last row.

function createDocument () {
  var headers,i,L,scriptProps,tactics;
  var endRowToRange,rangeForDate,sh,sheetTabName,ss,ssFileID,startRowToRange,templateId;
 
  ssFileID = '1tkcP1lA8QKIuAnur7WnGrHiVI1hrXdzRZPwp3ruSjrI';
  templateId = '1uOJ_r2nqfgS8tE-__-CvJP7nyObPLbmqpwQaN2iq8QA';
  sheetTabName = "Enter sheet tab name here";
 
  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
 
  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
 
  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 = Sheets.Spreadsheets.Values.get(ssFileID,'A1:I1');
  tactics = Sheets.Spreadsheets.Values.get(ssFileID,rangeForDate);

Hi,

[Matthieu Verneau]

Now on the second run the startRow value is 1061.

scriptProps.setProperty('startRow',endRowToRange + 1); // Save new start value

I do not understand which we increment by 1 the endRowToRange ? Is it supposed to be the last row processed?

[Matthieu Verneau]

On this line :

endRowToRange = sh.getLastRow();//The end row number will always be the last row in the sheet tab

I think that the value should be the last filled row and not the last empty row. Is it possible to target it like that ?

It should be the last filled row and then the startRow should be the last filled row + 1?

[Matthieu Verneau]

I mean, it can be the last filled row with the first column as the first column will always get a value.

Ax = a number.

[Alan Wells]

If you were setting the start and end values manually each time, how would you do it?

Then configure the code to do the same.

You can get the last row with values.  That's the current information.

The new start row needs to come from the saved value.

The new start row needs to either be adjusted before it's saved, or before the new instance of the code runs.

[Matthieu Verneau]

Hi Alan,

I don’t know exactly why I need to set up the value manually. I guess I don’t, or maybe only for the first run of the app?

What you said is totally correct and it’s the way to make it works.

As you said, I need to get the last value filled then store it on the start row and then increment it by 1.

I read on many documentation that we can use on google sheet the function ISBLANK(). But I’m not quite sure that it works on google app script, do you know something similar ?

Thanks again and again. I’m sorry to ask you so much help and questions.
Regards,
Matt

[Alan Wells]

You can check for a blank value in a cell with code instead of using the ISBLANK() cell function.

You need to get the cell value and then test for an empty string.

To test for an empty string use two equal signs and quotes

if (cellValue == "") {//Test for a blank value
  //its an empty cell

}

You don't need to manually set the values every time in the code.  I'm trying to get you to "think through" the logic,

in order to understand how the code needs to be written.

Even for something relatively easy, there are hundreds of errors that can happen.

From the code, did you see how to save the row value, and then get it back?

To get the value:

  var scriptProps = PropertiesService.getScriptProperties();
  var startRowToRange = scriptProps.getProperty('startRow');//Get the stored row value

To Save (set) value

scriptProps.setProperty('startRow', a value goes here); // Save value to Property Service

'startRow' is the key name.

I want you to understand the code in case you need to modify it, or write some other code.

[Matthieu Verneau]

Hi Alan,

Thank you for your highlights.

I'm trying to get it works since 4 hours but, I do not understand how to do it.

I tried the get and store de last empty cell of the column A into cellValue but it's not working..

I'm missing something for sure and I don't know how the script can start at the first run if we do not add the value manually.

I also don't know what I should add into this if loop :

if (cellValue == "") {//Test for a blank value
  //its an empty cell

}

I tried everything.

Sorry to bother you again with this, but I think that I'll need more explanations to clearly understand how to make this work.

Could you please give me some more information and codes...

Thank you 1000 times and more.

Matt

[Alan Wells]

There are millions of ways that it won't work, but only a few ways that it will work.  If you live to be 1,000 years old, then you might not be able to try every possible way.  I know this because I've wasted a lot of time trying to make something work, and failing before spending the time to understand what I'm doing.  I think you need to get some more basic programing knowledge.