Fill a template from a spreadsheet

So you got an event happening every week or month and you use the same advert or agenda each time. You hate having to open up the old document and change the same parts of the document over and over again. I use Google Drive very often to manage my Toastmaster club’s agendas, accounts and mailing list. Here is a basic example of using a spreadsheet to populate an advert or agenda easily (fill a template from a spreadsheet): You need a document as a template and a spreadsheet to host the information. Go to your Google Drive and create a document and design your advert with place holders for the changing information, see the picture below.

This gives me the following place holders: %date%, %bigdate%, %route%, %time%, %runners% Now make a spreadsheet with columns for the place holders, see the picture below.

Next you need to open up the script editor and add the code. In the spreadsheet go to ‘tools’ -> ‘script editor’ and the options panel will open up. Choose ‘Blank Project’ like in the picture below.

You are now in the scripts editor as seen in the picture below. Note it has a debug button which helps sometimes and a log view.

Take out the default code and add the following to make a menu called ‘extra’ with a button ‘Create Advert’ that will run a function called ‘MakeAdvert’.

//menu for the making adding to the advert
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var menu = [{
    name: "Create Advert",
    functionName: "MakeAdvert"
  }];
  sheet.addMenu("extra", menu); //a menu called extra
}

function MakeAdvert(){
}

Inside the function you need to read all the values of the spreadsheet to get the information. I want to be able to go back and create previous versions of the advert (it was useful in a different application). So I want to select the row/date of the advert I want to make.

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var rangeSheet = sheet.getDataRange().getValues(); //values of the speadsheet
var thisMeeting = sheet.getActiveCell().getRowIndex()-1; //get the selected row index for the advert I want to make

Next you need to pull in the template (the template ID is part of the document url, see pic) and make a copy of the document so that you can replace the place holders.

var templateId = '1kI1tkmKjONUw3GeEdhAPIM2ytbDbLQDHldmCUVaB644'; //id of the template for the document
var docId = DocsList.getFileById(templateId).makeCopy().getId(); //making a copy for the current advert
var doc = DocumentApp.openById(docId);
var body = doc.getActiveSection(); //the current advert

Now you need to replace the place holders with information.

var keys = {
    date: Utilities.formatDate(rangeSheet[thisMeeting][0], "GMT+02:00", "d/MM/yy"), //formated date and [current advert row][date col]
    bigdate: Utilities.formatDate(rangeSheet[thisMeeting][0], "GMT+02:00", "EEE d MMM yy"), //formated date and [current advert row][date col]
    route: rangeSheet[thisMeeting][2],
    time: Utilities.formatDate(rangeSheet[thisMeeting][1], "GMT+02:00", "H:mm"), //formated time and [current advert row][date col]
    runners: rangeSheet[thisMeeting][3]

  };
for ( var k in keys ){
  body.replaceText("%" + k + "%", keys[k]);
}

doc.setName('Running Advert ' + Utilities.formatDate(rangeSheet[thisMeeting][0], "GMT+02:00", "EEE d MMM yy")); //saving setting name with a date
doc.saveAndClose();

Time to test the code, save the code with the ‘save’ icon. Then go to ‘Run’ -> ‘onOpen’ and accept the authorization dialog that pops up. Then the ‘extra’ menu will show up in the the spreadsheet page, you may have to refresh the page initially.

Then follow this recipe to make the advert:

1. Select the row/date for the advert you want to make.
2. Select ‘Create Advert’ to make the advert.
3. A new document will show up in your Google Drive.

Now your advert with the current information will show.

Let me know if this helped you.