Produce Google Docs from a Google Sheet

Your company can use Google Apps Script to extract information from the columns and rows of a Google Sheet and make personalized Google Docs that include just the information needed. This can make your organization and the people running it more effective.

Here’s an illustration. Envision a midsize brick-and-click retailer. The business has developed a Google Sheet with a comprehensive marketing plan for all its key suppliers. The plan describes specific marketing strategies by month and includes the budget for each strategy.

The business’s Google Sheet contains a comprehensive marketing plan for all its key suppliers. This example shows only four of them for February 2018.

One of the provider’s marketing pros is tasked with providing each provider a marketing proposal. If the provider accepts the proposal, it is going to cover co-op advertising, which ordinarily is about half the cost of the planned promotion.

This task sounds simple enough. But what if this advertising specialist required to make proposals for 100 providers?

That might require typing (or cutting and pasting) data in the Google Sheet to all 100 Google Docs manually, a very time-consuming undertaking.

Alternately, a marketer may write a brief Google Apps Script and automate the procedure.

Google Apps Script

Google Apps Script is a simple scripting language based on JavaScript. According to Google, it enables you to”increase the ability of your beloved Google apps” including Docs and Sheets.

In practice, this means that you could use Google Apps Script to personalize Google apps and create new capabilities. In this case, I will take the information out of a sample marketing-tactic spreadsheet and use it to create several advertising proposals.

Associate Google Apps Script

If you’re using Google Apps Script for the first time, you’ll have to connect 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.
  • Locate Google Apps Script.
  • Permit it.

Associate Google Apps Scripts with your Google Drive.

Create a Google Docs Template

Now that you’ve got a spreadsheet full of data, and you’ve associated Google Apps Script with your Google Drive, it’s time to make a template to your advertising plans in Google Docs.

To do so, simply create a Google Doc that has all the replicated copy for the advertising proposal. If you will need to use content in the spreadsheet, such as the provider’s business name, use a simple placeholder.

In the picture below, ##Supplier## is used as a placeholder for the provider field in the spreadsheet.

Creating a template is as simple as creating a Google Doc. Use unique placeholders on your template so you can replace them with info from the Google Sheet.

See also:

Create a Google Apps Script

To begin a new Google Apps Script, open Google Drive, and right click. From the menu that opens, select”More,” then select”Google Apps Script.” If you do not see Google Apps Script as an option, ensure you properly connected it with your Google Drive.

Create a new Google Apps Script from anywhere in Drive using a right click.

When you click on 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 job in the Google Apps Script editor.

From the Apps Scripts editor:

  • Click Resources.
  • Click Advanced Google Services.
  • Find Sheets API and turn it on.
  • Click Google API Console.
  • Sort”Sheets API” from the search box.
  • Click Enable API.
  • Return to the editor and click on the OK button.

It’ll be necessary to turn on some APIs, such as the Sheets API.

Set Factors for the Sheet and Template

Turning our attention back to the editor, we’ll start with a function called createDocument().

This function starts with three factors: headers, tactics, templateId.

var headers = Sheets.Spreadsheets.Values.get('1U-6...', 'A2:I2');
Var approaches = Sheets.Spreadsheets.Values.get('1U-6...','A3:' I6');
var templateId ='18PzF...;

The first two of these variables get into the Google Sheet with our advertising tactics. Sheets.Spreadsheets.Values.get() accesses the Sheet and takes two parameters.

The first parameter is the ID of the spreadsheet. The simplest way to locate this ID is to look at the address bar in your web browser when you start the Sheet.

You can get the ID for any Google Sheet or Doc in the address bar when that Sheet or Doc is available on your internet browser.

The second parameter describes the rows we’ll access. In this instance, the headers are in row 2 between column A and column , thus”A2:I2.” The values that we wish to get are in row 3 through row 6, and from column A to column I. We use”A3:I6″ to specify the region.

The headers are in row 2 between column A and column , thus”A2:I2.” The values are in row 3 through row 6, and from column A to column I. We use”A3:I6″ to specify the region.

The third factor — templateId — is the ID for the template record you created. You might also find this ID from the address bar when you have the template available.

Loop On the Rows from the Sheet

Google Apps Script doesn’t have all the features found in the latest versions of JavaScript. So while we would like to use an array function to utilize the data in the Sheet, we’ll instead have to use a for loop.

For(var I = 0; I < tactics.values.length; i++){

We begin a for loop by placing the initial value of a variable, in this instance, I. We put the limit of the loop into the period of our array. Finally, we increment the variable I till it reaches the limit.

The Google Apps Script editor includes a couple of debugging and development tools. So in the event you wish to see exactly what the Sheet’s API is coming, you can get a logger.

Logger.log(tactics);

you’ll have to run the function, then click on”View, Logs” to see the output.

Back to the loop, we create a variable to store the provider name.

Var provider = tactics.values[I][0];

The tactics thing includes a property of worth , which is a range of arrays representing each row we asked in the Sheets API. The iterator, [I], are 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, that’s the title of the supplier. Specifically, since we began at row 3 for strategies, the first row and first column will be A3, which has the provider name,”Awesome Inc.” The next time the loop runs tactics.values[I][0] will point to A4 and the provider, Best Company.

The tactics object within our script is a range of arrays representing the four rows of provider marketing tactics in the Google Sheet. The first field [0] at the first row is the provider name, Awesome Inc..

Copy the Template

The next line from the code will copy our template and catch the ID of the copied document.

Var documentId = DriveApp.getFileById(templateId).makeCopy().getId();

Notice that we’re using the template ID factor from above. Also, we’re using the DriveApp API. You might have to turn this on in precisely the identical manner that you turned on the Sheets API.

This command gets the template doc with the template ID. It creates a copy of the template at the exact same Drive directory, and it will get the ID for the new document so we can use it afterwards.

Right now, the title of our recently copied document is”Copy of” whatever you called your template, which isn’t so helpful, so we’ll change the file’s name.

DriveApp.getFileById(documentId).setName('2018' + provider +' Marketing Proposal');

We get the document using the record ID we recorded in the preceding step. We set the name. Notice that Google Apps Script uses the identical style of concatenation as JavaScript, so we can join strings such as 2018 with factors like provider .

Update the Template

The next line from the Apps Script accesses the body of this new document.

Var body = DocumentApp.openById(documentId).getBody();

Notice that we’re again using the record ID captured when we copied the template, but this time we’re interacting with the DocumentApp API.

Our first change is to upgrade each instance of our provider name placeholder, ##Supplier##, in the template.

Body.replaceText('##Supplier##', provider )

Notice that replaceText takes two parameters. First, there’s the text we wish to replace. Next is the factor representing the provider name.

The last step is to include the list of strategies to the advertising proposal. To do this we call another function, parseTactics, passing it the header values (i.e.,”provider,””YouTube Commercial,””Hulu Commercial”); the row of advertising strategies; and the body of the promotion proposal document.

ParseTactics(headers.values[0], tactics.values[I], body);

The parseTactics function loops over every strategy in the row and adds it into the advertising proposal if it has a value.

function parseTactics(headers, tactics, body)undefined

Notice that we’re setting the initial value of this variable I to 1 instead of 0. This is because the 0 position in the array is the provider name. We want to start with the 1 position, which is the value of this YouTube Commercial tactic.

for(var i = 1; i < tactics.length; i++)

JavaScript arrays are zero-based. So the first item in the array is at the 0 position. The next thing is at the 1 position.

We’re using a technique known as short-circuit evaluation to bring each tactic.

' + approaches [I] +' net')
        .setGlyphType(DocumentApp.GlyphType.BULLET);

We check to find out whether the strategy has a value. Specifically, we’re asking if this”strategy isn’t equivalent to an empty string.”

tactics[i] ! = "

Then we use the “and” operator, &&. This says that the two things have to be true. Thus, if the tactics field is empty, it’s not accurate and our following line of code won’t run.

Body.appendListItem(headers[I] +' |' + approaches [I] +' net')

The following section of code provides a list item (such as a bulleted list) into the advertising proposal document. By default, the API would like to make an ordered list, as in 1., 2., 3. Next we set the listing glyph type to BULLET.

.setGlyphType(DocumentApp.GlyphType.BULLET);

Produce the Records

we’ve completed the code required to create marketing proposals in the spreadsheet. We simply have to run our code.

From the Google Apps Script editor, select”createDocument” in the drop-down menu and click on”Run.”

Be sure you have the appropriate function showing from the drop-down menu then click”Run.”

This will generate four example advertising proposals in exactly the exact same folder as our template.

The new advertising proposals will be generated in exactly the same folder as the template.

All the promotion proposals will include the provider’s name and the strategies the advertising department had in mind for them.

The finished marketing proposals are tailored to each provider and include just the strategies proposed for it.

Here’s the entire script used in this example.

function createDocument() undefined

function parseTactics(headers, tactics, body){ 

for(var i = 1; i < tactics.length; i++)undefined

►►► cloud-based POS software compatible with multiple platforms including Magento b2b, Shopify automation, Magento 2 development agency, Magento development services and BigCommerce Automation

Leave a Reply

Your email address will not be published.