Your business can use Google Apps Script to pull data from the rows and columns of a Google Sheet and create individualized Google Docs that include only the information needed. This can make your business and the folks running it more productive.
Here’s an example. Imagine a mid-sized brick-and-click retailer. The company has developed a Google Sheet with a detailed marketing plan for each of its key suppliers. The plan describes specific marketing tactics by month and includes the budget for each tactic.
The company’s Google Sheet contains a detailed marketing plan for each of its key suppliers. This example shows only four of them for February 2018.
One of the company’s marketing specialists is tasked with giving each supplier a marketing proposal. If the supplier accepts the proposal, it will pay co-op advertising, which in most cases is about half the cost of the planned promotion.
This task sounds easy enough. But what if this marketing specialist needed to create proposals for 100 suppliers?
That might require typing (or at least cutting and pasting) data from the Google Sheet to each of 100 Google Docs manually, a very time-consuming task.
Alternatively, a marketer could write a short Google Apps Script and automate the process.
Google Apps Script
Google Apps Script is a simple scripting language based on JavaScript. According to Google, it allows you to “increase the power of your favorite Google apps” including Docs and Sheets.
In practice, this means that you can use Google Apps Script to customize Google apps and create new capabilities. In this example, I will take the data from a sample marketing-tactic spreadsheet and use it to create several marketing proposals.
Associate Google Apps Script
If you are using Google Apps Script for the first time, you will need to associate it with your Google Drive.
To do this:
- Open Google Drive.
- Click on the sprocket icon.
- Click Settings.
- Click Manage Apps.
- Click Connect more apps.
- Find Google Apps Script.
- Enable it.
Associate Google Apps Scripts with your Google Drive.
Create a Google Docs Template
Now that you have a spreadsheet full of data, and you have associated Google Apps Script with your Google Drive, it is time to create a template for the marketing plans in Google Docs.
To do this, simply create a Google Doc that has all of the repeated copy for the marketing proposal. If you need to use content from the spreadsheet, like the supplier’s business name, use a simple placeholder.
In the image below, ##Supplier## is used as a placeholder for the supplier field from the spreadsheet.
Creating a template is as easy as creating a Google Doc. Use unique placeholders in your template so that you can replace them with information from the Google Sheet.
Create a Google Apps Script
To start a new Google Apps Script, open Google Drive, and right click. In the menu that opens, select “More,” then select “Google Apps Script.” If you don’t see Google Apps Script as an option, make certain you properly associated it with your Google Drive.
Create a new Google Apps Script from anywhere in Drive with a right click.
—
When you click Google Apps Script, you will see a new tab with the Apps Script editor. Then, turn on the Google Sheets API.
You will do your work in the Google Apps Script editor.
In the Apps Scripts editor:
- Click Resources.
- Click Advanced Google Services.
- Locate Sheets API and turn it on.
- Click Google API Console.
- Type “Sheets API” in the search box.
- Click Enable API.
- Go back to the editor and click the OK button.
It will be necessary to turn on some APIs, including the Sheets API.
—
Set Variables for the Sheet and Template
Turning our attention back to the editor, we will begin with a function called createDocument().
This function begins with three variables: headers, tactics, 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 of these variables access the Google Sheet with 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 to look in the address bar on your web browser when you open the Sheet.
You can find the ID for any Google Sheet or Doc in the address bar when that Sheet or Doc is open in your web browser.
—
The second parameter describes the rows we will access. In this example, the headers are in row 2 between column A and column I, thus “A2:I2.” The values that we want to access are in row 3 through row 6, and also from column A to column I. So we use “A3:I6” to define the area.
The headers are in row 2 between column A and column I, thus “A2:I2.” The values are in row 3 through row 6, and also from column A to column I. So we use “A3:I6” to define the area.
The third variable — templateId — is the ID for the template document you created. You can also find this ID in the address bar when you have the template open.
Loop Over the Rows in the Sheet
Google Apps Script does not have all of the features found in the most recent versions of JavaScript. So while we might like to use an array function to work with the data from the Sheet, we will instead need to use a for loop.
for(var i = 0; i < tactics.values.length; i++){
We start a for loop by setting the initial value of a variable, in this case, i. Next we set the limit of the loop to the length of our array. Finally, we increment the variable i until it reaches the limit.
The Google Apps Script editor has a few debugging and development tools. So if you want to see the what the Sheet’s API is returning, you can access a logger.
Logger.log(tactics);
You will need to run the function, then click “View, Logs” to see the output.
Back to the loop, we create a variable to store the supplier name.
var supplier = tactics.values[i][0];
The tactics object has a property of values, 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 the loop runs.
The [0] represents the first column in the sheet, which is the name of the supplier. Specifically, since we started at row 3 for tactics, the first row and first column will be A3, which has the supplier name, “Awesome Inc.” The next time the loop runs tactics.values[i][0] will point to A4 and the supplier, Best Company.
The tactics object in our script is an array of arrays representing the four rows of supplier marketing tactics from the Google Sheet. The first field [0] in the first row is the supplier name, Awesome Inc.
Copy the Template
The next line in the code will copy our template and capture the ID of the copied document.
var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
Notice that we are using the template ID variable from above. Also, we are using the DriveApp API. You may need to turn this on in the same way that you turned on the Sheets API.
This command first gets the template doc using the template ID. Next, it makes a copy of the template in the same Drive directory, and finally, it gets the ID for the new document so we can use it later.
At the moment, the name of our newly copied document is “Copy of” whatever you named your template, which is not very helpful, so we will change the file’s name.
DriveApp.getFileById(documentId).setName('2018 ' + supplier + ' Marketing Proposal');
First, we get the file using the document ID we captured in the previous step. Next, we set the name. Notice that Google Apps Script uses the same style of concatenation as JavaScript, so we can connect strings like 2018 with variables like supplier.
Update the Template
The next line in the Apps Script accesses the body of the new document.
var body = DocumentApp.openById(documentId).getBody();
Notice that we are again using the document ID captured when we copied the template, but this time we are interacting with the DocumentApp API.
Our first change is to update each instance of our supplier name placeholder, ##Supplier##, in the template.
body.replaceText('##Supplier##', supplier)
Notice that replaceText takes two parameters. First, there is the text we want to replace. Next is the variable representing the supplier name.
The final step is to add the list of tactics to the marketing proposal. To do this we call a second function, parseTactics, passing it the header values (i.e., “supplier,” “YouTube Commercial,” “Hulu Commercial”); the row of marketing tactics; and the body of the marketing proposal document.
parseTactics(headers.values[0], tactics.values[i], body);
The parseTactics function loops over 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 are setting the initial value of the variable i to 1 rather than 0. This is because the 0 position in the array is the supplier name. We want to begin with the 1 position, which will be the value of the YouTube Commercial tactic.
for(var i = 1; i < tactics.length; i++)
JavaScript arrays are zero-based. So the first item in the array is in the 0 position. The second item is in the 1 position.
We are using 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 to see if the tactic has a value. Specifically, we are asking if this “tactic is not equal to an empty string.”
tactics[i] != ''
Then we use the “and” operator, &&. This says that both things must be true. Thus, if the tactics field is empty, it is not true and our next line of code will not run.
body.appendListItem(headers[i] + ' | ' + tactics[i] + ' net')
The next section of code adds a list item (like a bulleted list) to the marketing proposal document. By default, the API wants to create an ordered list, as in 1., 2., 3. So next we set the list glyph type to BULLET.
.setGlyphType(DocumentApp.GlyphType.BULLET);
Create the Documents
We have completed the code needed to generate marketing proposals from the spreadsheet. We simply need to run our code.
In the Google Apps Script editor, select “createDocument” from the drop-down menu and click “Run.”
Be certain that you have the correct function showing in the drop-down menu then click “Run.”
—
This will generate four example marketing proposals in the same folder as our template.
The new marketing proposals will be created in the same folder as the template.
—
Each of the marketing proposals will include the supplier’s name and the tactics the marketing department had in mind for them.
The completed marketing proposals are tailored to each supplier and include only the tactics proposed for it.
—
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);
}
}
}