What you are promoting can use Google Apps Script to tug knowledge from the rows and columns of a Google Sheet and create individualized Google Docs that embrace solely the knowledge wanted. This could make your enterprise and the parents operating it extra productive.
Right here’s an instance. Think about a mid-sized brick-and-click on retailer. The corporate has developed a Google Sheet with an in depth marketing plan for every of its key suppliers. The plan describes particular marketing techniques by month and consists of the finances for every tactic.
One of many firm’s marketing specialists is tasked with giving every provider a marketing proposal. If the provider accepts the proposal, it is going to pay co-op promoting, which usually is about half the price of the deliberate promotion.
This activity sounds straightforward sufficient. However what if this marketing specialist wanted to create proposals for one hundred suppliers?
Which may require typing (or a minimum of chopping and pasting) knowledge from the Google Sheet to every of one hundred Google Docs manually, a really time-consuming activity.
Alternatively, a marketer might write a brief Google Apps Script and automate the method.
Google Apps Script
In follow, because of this you need to use Google Apps Script to customise Google apps and create new capabilities. On this instance, I'll take the info from a pattern advertising-tactic spreadsheet and use it to create a number of advertising proposals.
Affiliate Google Apps Script
In case you are utilizing Google Apps Script for the primary time, you will want to affiliate it together with your Google Drive.
To do that:
- Open Google Drive.
- Click on on the sprocket icon.
- Click on Settings.
- Click on Handle Apps.
- Click on Join extra apps.
- Discover Google Apps Script.
- Allow it.
Create a Google Docs Template
Now that you've a spreadsheet full of knowledge, and you've got related Google Apps Script together with your Google Drive, it's time to create a template for the advertising plans in Google Docs.
To do that, merely create a Google Doc that has all the repeated copy for the advertising proposal. If it's essential use content material from the spreadsheet, just like the provider’s enterprise identify, use a easy placeholder.
Within the picture under, ##Provider## is used as a placeholder for the provider subject from the spreadsheet.
Create a Google Apps Script
To start out a brand new Google Apps Script, open Google Drive, and proper click on. Within the menu that opens, choose “Extra,” then choose “Google Apps Script.” In case you don’t see Google Apps Script as an choice, make sure you correctly related it together with your Google Drive.
Whenever you click on Google Apps Script, you will notice a brand new tab with the Apps Script editor. Then, activate the Google Sheets API.
Within the Apps Scripts editor:
- Click on Assets.
- Click on Superior Google Providers.
- Find Sheets API and switch it on.
- Click on Google API Console.
- Sort “Sheets API” within the search field.
- Click on Allow API.
- Return to the editor and click on the OK button.
Set Variables for the Sheet and Template
Turning our consideration again to the editor, we'll start with a perform referred to as createDocument().
This perform begins with three variables: headers, techniques, templateId.
var headers = Sheets.Spreadsheets.Values.get('1U-6...', 'A2:I2'); var techniques = Sheets.Spreadsheets.Values.get('1U-6...', 'A3:I6'); var templateId = '18PzF...;
The primary two of those variables entry the Google Sheet with our advertising techniques. Sheets.Spreadsheets.Values.get() accesses the Sheet and accepts two parameters.
The primary parameter is the ID of the spreadsheet. The simplest strategy to discover this ID is to look within the handle bar in your net browser whenever you open the Sheet.
The second parameter describes the rows we'll entry. On this instance, the headers are in row 2 between column A and column I, thus “A2:I2.” The values that we need to entry are in row three via row 6, and in addition from column A to column I. So we use “A3:I6” to outline the world.
The third variable — templateId — is the ID for the template doc you created. You may also discover this ID within the tackle bar when you've got the template open.
Loop Over the Rows within the Sheet
for(var i = zero; i < techniques.values.size; i++)
We begin a for loop by setting the preliminary worth of a variable, on this case, i. Subsequent we set the restrict of the loop to the size of our array. Lastly, we increment the variable i till it reaches the restrict.
The Google Apps Script editor has a couple of debugging and improvement instruments. So if you wish to see the what the Sheet’s API is returning, you possibly can entry a logger.
You will have to run the perform, then click on “View, Logs” to see the output.
Again to the loop, we create a variable to retailer the provider identify.
var provider = techniques.values[i];
The techniques object has a property of values, which is an array of arrays representing every row we requested from the Sheets API. The iterator, [i], would be the first row the primary time the loop runs and the second row the subsequent time the loop runs.
The  represents the primary column within the sheet, which is the identify of the provider. Particularly, since we began at row three for techniques, the primary row and first column might be A3, which has the provider identify, “Superior Inc.” The subsequent time the loop runs techniques.values[i] will level to A4 and the provider, Greatest Firm.
Copy the Template
The subsequent line within the code will copy our template and seize the ID of the copied doc.
var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
Discover that we're utilizing the template ID variable from above. Additionally, we're utilizing the DriveApp API. You might want to show this on in the identical means that you simply turned on the Sheets API.
This command first will get the template doc utilizing the template ID. Subsequent, it makes a replica of the template in the identical Drive listing, and eventually, it will get the ID for the brand new doc so we will use it later.
In the meanwhile, the identify of our newly copied doc is “Copy of” no matter you named your template, which isn't very useful, so we'll change the file’s identify.
DriveApp.getFileById(documentId).setName('2018 ' + provider + ' Advertising Proposal');
Replace the Template
The subsequent line within the Apps Script accesses the physique of the brand new doc.
var physique = DocumentApp.openById(documentId).getBody();
Discover that we're once more utilizing the doc ID captured once we copied the template, however this time we're interacting with the DocumentApp API.
Our first change is to replace every occasion of our provider identify placeholder, ##Provider##, within the template.
Discover that replaceText takes two parameters. First, there's the textual content we need to exchange. Subsequent is the variable representing the provider identify.
The ultimate step is so as to add the record of techniques to the advertising proposal. To do that we name a second perform, parseTactics, passing it the header values (i.e., “provider,” “YouTube Business,” “Hulu Business”); the row of selling techniques; and the physique of the advertising proposal doc.
parseTactics(headers.values, techniques.values[i], physique);
The parseTactics perform loops over every tactic within the row and provides it to the advertising proposal if it has a worth.
perform parseTactics(headers, techniques, physique) for(var i = 1; i < techniques.size; i++) techniques[i] != '' && physique.appendListItem(headers[i] + ' | ' + techniques[i] + ' internet').setGlyphType(DocumentApp.GlyphType.BULLET);
Discover that we're setting the preliminary worth of the variable i to 1 relatively than zero. It's because the zero place within the array is the provider identify. We need to start with the 1 place, which would be the worth of the YouTube Business tactic.
for(var i = 1; i < techniques.size; i++)
We're utilizing a way referred to as brief-circuit analysis so as to add every tactic.
techniques[i] != '' && physique.appendListItem(headers[i] + ' | ' + techniques[i] + ' internet') .setGlyphType(DocumentApp.GlyphType.BULLET);
First, we examine to see if the tactic has a worth. Particularly, we're asking if this “tactic shouldn't be equal to an empty string.”
techniques[i] != ''
Then we use the “and” operator, &&. This says that each issues have to be true. Thus, if the techniques area is empty, it isn't true and our subsequent line of code won't run.
physique.appendListItem(headers[i] + ' | ' + techniques[i] + ' internet')
The subsequent part of code provides an inventory merchandise (like a bulleted listing) to the advertising proposal doc. By default, the API needs to create an ordered record, as in 1., 2., three. So subsequent we set the record glyph sort to BULLET.
Create The Paperwork
We have now accomplished the code wanted to generate advertising proposals from the spreadsheet. We merely have to run our code.
Within the Google Apps Script editor, choose “createDocument” from the drop-down menu and click on “Run.”
It will generate 4 instance advertising proposals in the identical folder as our template.
Every of the advertising proposals will embrace the provider’s identify and the techniques the advertising division had in thoughts for them.
Right here is the entire script used on this instance.
perform createDocument() var headers = Sheets.Spreadsheets.Values.get('1U-6Fa6GF62dLI0NAhAMWdaSsEWvY4FDhDWLUVxaCv24', 'A2:I2'); var techniques = Sheets.Spreadsheets.Values.get('1U-6Fa6GF62dLI0NAhAMWdaSsEWvY4FDhDWLUVxaCv24', 'A3:I6'); var templateId = '18PzFAptRi36PR8CvJ2rVr3IVCGBMCNoCsG7UpOymPHc'; for(var i = zero; i < techniques.values.size; i++) var provider = techniques.values[i]; //Make a replica of the template file var documentId = DriveApp.getFileById(templateId).makeCopy().getId(); //Rename the copied file DriveApp.getFileById(documentId).setName('2018 ' + provider + ' Advertising Proposal'); //Get the doc physique as a variable var physique = DocumentApp.openById(documentId).getBody(); //Insert the provider identify physique.replaceText('##Provider##', provider) //Append techniques parseTactics(headers.values, techniques.values[i], physique); perform parseTactics(headers, techniques, physique) for(var i = 1; i < techniques.size; i++) techniques[i] != '' && physique.appendListItem(headers[i] + ' | ' + techniques[i] + ' internet').setGlyphType(DocumentApp.GlyphType.BULLET);