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.
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.
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.
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.
—
When you click Google Apps Script, you will see a new tab with the Apps Script editor. Then, turn on the Google Sheets API.
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.
—
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.
—
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 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.
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++)
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.”
—
This will generate four example 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); } } }