Imagine having to create marketing proposals for 100 suppliers manually. It would be an incredibly time-consuming task, right? Well, not anymore! With Google Apps Script, you can automate this process and increase productivity for your business and team.
What is Google Apps Script?
Google Apps Script is a simple scripting language based on JavaScript. According to Google, it allows you to extend the power of your favorite Google apps, including Docs and Sheets.
In practice, this means you can use Google Apps Script to customize Google applications and create new features. In this example, we will use data from a Google Spreadsheet on marketing tactics and use it to create multiple marketing proposals.
Associating Google Apps Script with Google Drive
If you are using Google Apps Script for the first time, you’ll need to associate it with your Google Drive. Here’s how you can do it:
- Open Google Drive.
- Click on the gear icon.
- Select “Settings”.
- Click on “Manage Apps”.
- Click on “Connect more apps”.
- Search for “Google Apps Script”.
- Activate it.
Creating a Google Document Template
Now that you have a spreadsheet filled with data and have associated Google Apps Script with your Google Drive, it’s time to create a template for your marketing plans in Google Docs.
Simply create a Google document containing all the repetitive parts of your marketing proposal. If you need to use data from the spreadsheet, such as the supplier’s company name, use a simple placeholder.
In the example below, ##Supplier## is used as a placeholder for the supplier’s name from the spreadsheet.
Creating a Google Apps Script
To start a new Google Apps Script, open Google Drive, right-click, select “More”, and then select “Google Apps Script”. If you don’t see Google Apps Script as an option, make sure you have properly associated it with your Google Drive.
When you click on Google Apps Script, you’ll see a new tab with the Apps Script editor. Next, activate the Google Sheets API.
In the Google Apps Script editor:
- Click on “Resources”.
- Click on “Advanced Google services”.
- Locate “Sheets API” and activate it.
- Click on “Google API Console”.
- Type “Sheets API” in the search bar.
- Click on “Enable API”.
- Go back to the editor and click “OK”.
Defining Variables for the Spreadsheet and the Template
Returning to the editor, we’ll start with a function called “createDocument()”. This function begins with three variables: “headers”, “tactics”, and “templateId”.
var headers = Sheets.Spreadsheets.Values.get('1U-6...', 'A2:I2');
var tactics = Sheets.Spreadsheets.Values.get('1U-6...', 'A3:I6');
var templateId = '18PzF...';The first two variables access the Google Spreadsheet containing our marketing tactics. “Sheets.Spreadsheets.Values.get()” accesses the sheet and accepts two parameters.
The first parameter is the ID of the spreadsheet. The easiest way to find this ID is by looking at the address bar of your web browser when you open the spreadsheet.
The second parameter describes the rows we want to access. In this example, the headers are in row 2, between columns A and I, so “A2:I2”. The values we want to access are from row 3 to row 6, also from column A to column I. Therefore, we use “A3:I6” to define the range.
The third variable, “templateId”, is the ID of the template document you created. You can also find this ID in the address bar when you have the template open.
Iterating through the Spreadsheet Rows
Google Apps Script doesn’t have all the features of the latest JavaScript versions. So, while we would like to use an array function to work with spreadsheet data, we’ll have to use a “for” loop instead.
for(var i = 0; i < tactics.values.length; i++){
// code goes here
}We start a “for” loop by setting the initial value of a variable, in this case “i”. Then, we set the loop limit to the length of our array. Finally, we increment the “i” variable until it reaches the limit.
The Google Apps Script editor has some debugging and development tools, so if you want to see what the spreadsheet API returns, you can access a log.
Logger.log(tactics);You’ll need to run the function, then click on “View” and “Logs” to see the output.
Returning to the loop, we create a variable to store the supplier’s name.
var supplier = tactics.values[i][0];The “tactics” object has a “values” property, which is an array of arrays representing each row we requested from the Sheets API. The iterator “[i]” will be the first row the first time the loop runs and the second row the next time.
“[0]” represents the first column of the sheet, which is the supplier’s name. More specifically, since we started at row 3 for “tactics”, the first row and the first column will be A3, which contains the supplier’s name “Awesome Inc.”. The next time the loop runs, “tactics.values[i][0]” will point to A4 and the supplier “Best Company”.
Copying the Template
The next line of code will copy our template and retrieve the ID of the copied document.
var documentId = DriveApp.getFileById(templateId).makeCopy().getId();Notice that we are using the “templateId” variable from the previous section. Additionally, we are using the DriveApp API. You may need to activate it in the same way you activated the Sheets API.
This command first retrieves the template document using the template ID. Then, it makes a copy of the template in the same Drive folder, and finally, it retrieves the ID of the new document so we can use it later.
For now, the name of our new copied document is “Copy of” followed by the name you gave to your template, which is not very helpful, so let’s change the file name.
DriveApp.getFileById(documentId).setName('2018 ' + supplier + ' Marketing Proposal');We first retrieve the file using the document ID we captured in the previous step. Then, we set the name. Notice that Google Apps Script uses the same concatenation style as JavaScript, so we can concatenate strings like “2018” with variables like “supplier”.
Updating the Template
The next line of Apps Script accesses the body of the new document.
var body = DocumentApp.openById(documentId).getBody();Notice that we are using the document ID again, but this time, we are interacting with the DocumentApp API.
Our first change is to update each instance of our placeholder for the supplier’s name, ##Supplier##, in the template.
body.replaceText('##Supplier##', supplier);Notice that “replaceText” takes two parameters. First, there is the text we want to replace. Then, there is the variable representing the supplier’s name.
The next step is to add the list of tactics to the marketing proposal. For that, we call a second function, “parseTactics”, passing it the header values (e.g., “supplier”, “YouTube advertising”, “Hulu advertising”), the marketing tactics row, and the body of the marketing proposal document.
parseTactics(headers.values[0], tactics.values[i], body);The “parseTactics” function loops through each tactic in the row and adds it to the marketing proposal if it has a value.
function parseTactics(headers, tactics, body){
for(var i = 1; i < tactics.length; i++){
tactics[i] != '' && body.appendListItem(headers[i] + ' | ' + tactics[i] + ' net').setGlyphType(DocumentApp.GlyphType.BULLET);
}
}Notice that we set the initial value of the “i” variable to 1 instead of 0. This is because position 0 in the array is the supplier’s name. We want to start at position 1, which will be the value of the YouTube Commercial tactic.
for(var i = 1; i < tactics.length; i++)We use a technique called “short-circuit evaluation” to add each tactic.
{tactics[i] != '' && body.appendListItem(headers[i] + ' | ' + tactics[i] + ' net').setGlyphType(DocumentApp.GlyphType.BULLET);First, we check if the tactic has a value. Specifically, we ask if that “tactic is not equal to an empty string”.
tactics[i] != ''Then, we use the “and” operator, “&&”. This means that both things need to be true. So, if the tactics field is empty, that is not true, and our next line of code will not run.
body.appendListItem(headers[i] + ' | ' + tactics[i] + ' net')The next part of the code adds a list item (like a bullet list) to the marketing proposal document. By default, the API wants to create an ordered list, like 1, 2, 3. So, we set the list glyph type to BULLET.
.setGlyphType(DocumentApp.GlyphType.BULLET);Creating the Documents
We have completed the code needed to generate marketing proposals from the spreadsheet. We just need to run our code.
In the Google Apps Script editor, select “createDocument” from the dropdown menu and click “Run”.
This will generate four examples of marketing proposals in the same folder as our template.
Each of the marketing proposals will include the supplier’s name and the tactics the marketing department had in mind for them.
Here is the complete script used in this example:
function createDocument() {
var headers = Sheets.Spreadsheets.Values.get('1U-6Fa6GF62dLI0NAhAMWdaSsEWvY4FDhDWLUVxaCv24', 'A2:I2');
var tactics = Sheets.Spreadsheets.Values.get('1U-6Fa6GF62dLI0NAhAMWdaSsEWvY4FDhDWLUVxaCv24', 'A3:I6');
var templateId = '18PzFAptRi36PR8CvJ2rVr3IVCGBMCNoCsG7UpOymPHc';
for(var i = 0; i < tactics.values.length; i++){
var supplier = tactics.values[i][0];
//Make a copy of the template file
var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
//Rename the copied file
DriveApp.getFileById(documentId).setName('2018 ' + supplier + ' Marketing Proposal');
//Get the document body as a variable
var body = DocumentApp.openById(documentId).getBody();
//Insert the supplier name
body.replaceText('##Supplier##', supplier)
//Append tactics
parseTactics(headers.values[0], tactics.values[i], body);
}
}
function parseTactics(headers, tactics, body){
for(var i = 1; i < tactics.length; i++){
tactics[i] != '' && body.appendListItem(headers[i] + ' | ' + tactics[i] + ' net').setGlyphType(DocumentApp.GlyphType.BULLET);
}
}Now, you can automate the creation of marketing proposals from your Google spreadsheets using Google Apps Script! Remember to adapt the code to your specific data structure and run it in the Google Apps Script editor.
