Create Google Docs from a Google Spreadsheet

Your company can use Google Apps Script to extract data from rows and columns in a Google sheet and create custom Google documents containing only the necessary information. It can make your business and the people who run it more productive.

Here is an example. Imagine a medium-sized brick-and-click retailer. The company has developed a Google sheet with a detailed marketing plan for each of its major suppliers. The plan outlines specific marketing tactics by month and includes the budget for each tactic.

The Google Business Card contains a detailed marketing plan for each of its major suppliers for February 2018.

One of the company’s marketers is responsible for giving each vendor a marketing proposal. If the supplier accepts the proposal, it will pay for the cooperative advertising, which in most cases is about half of the cost of the planned promotion.

This task seems easy enough. But what if this marketing specialist were to create proposals for 100 suppliers?

This may require manually typing (or at least cutting and pasting) the Google Sheet data on each of the 100 Google documents, which is time consuming.

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, this allows you to “increase the power of your favorite Google applications,” including Google Docs and Sheets.

In practice, this means that you can use Google Apps Script to personalize Google apps and create new features. In this example, I will take the data from an example of a marketing-tactical spreadsheet and use it to create several marketing propositions.

Associate a Google Apps Script

If you use Google Apps Script for the first time, you’ll need to associate it with your Google Drive.

To do this:

  • Open Google Drive.
  • Click on the icon representing a pinion.
  • Click Settings.
  • Click Manage Applications.
  • Click More Apps.
  • Search for a Google Apps script.
  • Activate it.

Link Google Apps scripts to your Google Drive.

Create a Google Docs Model

Now that you have a spreadsheet full of data and you’ve linked Google Apps Script to your Google Drive, it’s time to create a template for marketing plans in Google Docs.

To do this, simply create a Google document containing all the repeated copies of the marketing proposal. If you must use the contents of the spreadsheet, such as the provider’s company name, use a simple placeholder.

In the image below, ## Supplier ## is used as the placeholder for the supplier field from the worksheet.

Creating a template is as easy as creating a Google document. Use unique placeholders in your template so that you can replace them with information from Google Sheet.

Creating a template is as simple as creating a Google document in your template so you can replace it with the information in the template. the Google sheet.

Create Google Apps Script

To launch a new Google Apps script, open Google Drive, then right click. In the menu that opens, select “More,” then select “Google Apps Script”. If you do not see Google Apps Script as an option, make sure to associate it correctly with your Google Drive.

Create a new Google Apps script from anywhere in Drive with a right click.

When you click Google Apps Script, a new tab appears in the Apps Script editor. Then enable the Google Sheets API.

You will be working in the Google Apps Script Editor.

In the publisher Apps Scripts:

  • Click Resources.
  • Click Advanced Google Services.
  • Locate the Sheets API and activate it.
  • Click Google API Console.
  • Type “Sheets API” into the search field.
  • Click Activate API.
  • Return to the editor and click the OK button.

It will be necessary to activate some APIs, including the Sheets API.

Defining variables for the sheet and the template

Returning to the editor, we will start with a function called createDocument () .

This function begins with three variables: headers tactics templateId .

 var headers = Sheets.Spreadsheets.Values.get ("1U-6 ...", "A2: I2 & # 39;);
var tactics = Sheets.Spreadsheets.Values.get (1U-6 ..., A3: I6 &);
var templateId = 18PzF ...;

The first two of these variables access 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 worksheet. The easiest way to find this ID is to look in the address bar of your web browser when you open the sheet.

You can find the ID of any Google sheet or document in the address bar when this sheet or document is open in your web browser.

The second parameter describes the lines we will access. In this example, the headers are in row 2 between column A and column I, so “A2: I2”. The values ​​we want to access are from row 3 to row 6, and from column A to column I. use “A3: I6” to define the area.

The headers are in row 2 between column A and column I, so “A2: I2”. The values ​​are in row 3 to 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 of the template document you created. You can also find this ID in the address bar when you have opened the template.

Loop on the lines in the sheet

Google Apps Script does not have all the features of the latest versions of JavaScript. So, although we would like to use a table function to work with the sheet data, we will need to use a loop for instead.

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

We begin a loop for by setting the initial value of a variable, in this case, i . Then we define the limit of the loop to the length of our array. Finally, we will increment the variable i until it reaches the limit.

The Google Apps Script Editor provides some debugging and development tools. So, if you want to see what returns the API of the sheet, you can access a recorder.

 Logger.log (tactical);

You will have to execute 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 object tactics has a property of values ​​ which is an array of paintings representing each line that we have requested from the Sheets API. The iterator, [i] will be the first row the first time the loop is executed and the second row the next time the loop is executed.

The [0] represents the first column of the sheet, which is the name of the supplier. Specifically, since we started on line 3 for the tactics, the first line and the first column will be A3, which has the supplier’s name, “Awesome Inc.” The next time the loop is executed tactics.values ​​[i] [0] will indicate A4 and the supplier, Best Company.

The tactical object in our script is an array of tables depicting the four lines of marketing tactics of the suppliers of the Google sheet.The first field [0] in the first row is the name of the provider, Awesome Inc.

Copy the model

The next line of code copies our template and captures the identifier of the copied document.

 var documentId = DriveApp.getFileById (templateId) .makeCopy (). GetId ();

Note that we use the template ID variable above. In addition, we use the DriveApp API. You may need to enable this option in the same way that you have enabled the Sheets API.

This command first gets the document template using the template ID. Then he creates a copy of the template in the same Drive directory, and finally, he gets the ID of the new document so we can use it later.

For the moment, the name of our newly copied document is “Copy of” what you called your template, which is not very useful, so we will change the file name.

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

First, we get the file using the document identifier that we captured at the previous step. Then we define the name. Note that Google Apps Script uses the same concatenation style as JavaScript, so we can connect strings like 2018 with variables like provider .

Update the model

The following line of the Apps script accesses the body of the new document.

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

Note that we use the captured document ID again when we copied the template, but this time we interact with the DocumentApp API.

Our first change is to update each instance of our vendor name placeholder, ## Vendor ## in the template.

 body.replaceText (## Vendor ##, supplier)

Note that replaceText takes two parameters. First, there is the text we want to replace. Next is the variable representing the name of the provider.

The last step is to add the list of tactics to the marketing proposal. To do this, we call a second function, parseTactics by passing it the header values ​​(that is, “provider”, “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 on every line tactic and adds it to the marketing proposal if it has a value.

 parseTactics function (headers, tactics, body) {

for (var i = 1; i <tactics.length; i ++) {
{tactical [i]! = & # 39; &&
body.appendListItem (headers [i] + + # tactics [i] + 'net'. setGlyphType (DocumentApp.GlyphType.BULLET);
}

}
}

Note that we set the initial value of i to 1 instead of 0. Indeed, the 0 position in the array is the vendor name. We want to start with position 1, which will be the value of the tactics of YouTube Commercial.

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

JavaScript arrays are based on zero. The first element of the array is in position 0. The second element is in position 1.

We use a technique called short-circuit evaluation to add each tactic.

 {Tactics [i]! = & # 39; &&
body.appendListItem (headers [i] + + # tactics [i] + net & # 39;)
.setGlyphType (DocumentApp.GlyphType.BULLET);
}

First, we check to see if the tactic has value. Specifically, we ask if this “tactic is not equal to an empty string.”

 Tactical [i]! = & # 39;

Then we use the operator “and”, && . That said, both things must be true. Thus, if the field Tactics is empty, this is not true and our next line of code will not work.

body.appendListItem (Headers [i] + '+ Tactics [i] +' net '(19459021)

The following code section adds a list item (such as a bulleted list) to the marketing proposal document. By default, the API wants to create an ordered list, as in 1., 2., 3. So, let’s define the glyph type of the list on BULLET .

 .setGlyphType (DocumentApp.GlyphType.BULLET);

Create Documents

We have completed the code needed to generate marketing proposals from the spreadsheet. We just have to execute our code.

In the Google Apps Script Editor, select “createDocument” from the drop-down menu and click “Run.”

Make sure you have the correct function in the drop-down menu, then click “Run.”

This will generate four examples of marketing proposals in the same folder as our template.

New marketing proposals will be created in the same folder as the template.

Each marketing proposal will include the vendor name and tactics that the marketing department had in mind for them.

The completed marketing proposals are tailored to each provider and include only the proposed tactics.

Here is the complete script used in this example.

 function createDocument () {
var headers = Sheets.Spreadsheets.Values.get (1U-6Fa6GF62dLI0NAhAMWdaSsEWvY4FDhDWLUVxaCv24 & # 39;, & # 39; A2: I2 & # 39;);
var tactics = Sheets.Spreadsheets.Values.get (1U-6Fa6GF62dLI0NAhAMWdaSsEWvY4FDhDWLUVxaCv24 & # 39;, & # 39; A3: I6 & # 39;);
var templateId = 18PzFAptRi36PR8CvJ2rVr3IVCGBMCNoCsG7UpOymPHc & # 39 ;;

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

var provider = 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' + Vendor + 'Marketing Proposal');

// Get the body of the document as a variable
var body = DocumentApp.openById (documentId) .getBody ();


// Insert the provider name
body.replaceText (## Vendor ##, supplier)

// Add tactics
parseTactics (headers.values ​​[0] tactics.values ​​[i] body);

}

}

parseTactics function (headers, tactics, body) {

for (var i = 1; i <tactics.length; i ++) {
{tactical [i]! = & # 39; &&
body.appendListItem (headers [i] + + # tactics [i] + 'net'. setGlyphType (DocumentApp.GlyphType.BULLET);
}

}
}