How To Google Sheets Data Into Doc Template

Your business can utilize 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 information technology more productive.

Here'due south an example. Imagine a mid-sized brick-and-click retailer. The company has developed a Google Canvass with a detailed marketing plan for each of its fundamental suppliers. The plan describes specific marketing tactics by calendar 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.

The company's Google Sheet contains a detailed marketing plan for each of its central suppliers. This case shows simply 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 advertizing, 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 to the lowest degree cutting and pasting) data from the Google Sail 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 linguistic communication based on JavaScript. According to Google, it allows y'all to "increase the power of your favorite Google apps" including Docs and Sheets.

In practice, this ways that you lot can utilize Google Apps Script to customize Google apps and create new capabilities. In this example, I will take the information from a sample marketing-tactic spreadsheet and utilise information technology to create several marketing proposals.

Acquaintance Google Apps Script

If yous are using Google Apps Script for the offset time, yous volition 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.
Observe Google Apps Script.
Enable it.

Associate Google Apps Scripts with your Google Drive.

Acquaintance Google Apps Scripts with your Google Drive.

Create a Google Docs Template

Now that y'all have a spreadsheet full of information, and you have associated Google Apps Script with your Google Drive, it is fourth dimension 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 y'all 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.

Creating a template is every bit easy equally creating a Google Medico. Utilize unique placeholders in your template so that you lot can replace them with information from the Google Sheet.

Create a Google Apps Script

To commencement a new Google Apps Script, open Google Drive, and correct click. In the carte that opens, select "More," then select "Google Apps Script." If you lot don't see Google Apps Script every bit an option, make certain you properly associated information technology 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 run across 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.

You lot 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.
Get back to the editor and click the OK push.

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

—

Ready Variables for the Sheet and Template

Turning our attention back to the editor, we will brainstorm with a role 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 ii of these variables access the Google Canvas with our marketing tactics. Sheets.Spreadsheets.Values.get() accesses the Canvass and accepts ii parameters.

The first parameter is the ID of the spreadsheet. The easiest way to find this ID is to wait in the accost bar on your spider web browser when you open up 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.

You lot can find the ID for any Google Canvass or Doc in the address bar when that Canvass or Physician is open in your web browser.

—

The second parameter describes the rows we will access. In this instance, the headers are in row 2 between cavalcade A and column I, thus "A2:I2." The values that nosotros desire to access are in row 3 through row 6, and also from column A to cavalcade I. So we apply "A3:I6" to ascertain the surface 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 headers are in row two between cavalcade A and column I, thus "A2:I2." The values are in row 3 through row half-dozen, and also from column A to cavalcade I. And 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 accept the template open up.

Loop Over the Rows in the Sail

Google Apps Script does not have all of the features found in the virtually recent versions of JavaScript. So while nosotros might like to use an array function to work with the data from the Sheet, we will instead need to utilize a for loop.

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

Nosotros start a for loop past setting the initial value of a variable, in this instance, i. Next we set the limit of the loop to the length of our array. Finally, we increment the variable i until information technology reaches the limit.

The Google Apps Script editor has a few debugging and development tools. So if yous want to see the what the Sheet'south API is returning, you can access a logger.

Logger.log(tactics);

Y'all volition need to run the function, then click "View, Logs" to see the output.

Back to the loop, nosotros create a variable to shop the supplier name.

var supplier = tactics.values[i][0];

The tactics object has a property of values, which is an assortment of arrays representing each row we requested from the Sheets API. The iterator, [i], will be the beginning row the first time the loop runs and the second row the side by side time the loop runs.

The [0] represents the kickoff column in the sail, which is the proper noun of the supplier. Specifically, since we started at row 3 for tactics, the commencement row and first column will be A3, which has the supplier name, "Crawly Inc." The side by side 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 assortment of arrays representing the 4 rows of supplier marketing tactics from the Google Sheet. The beginning field <em>[0]</em> in the first row is the supplier name, Awesome Inc.

The tactics object in our script is an array of arrays representing the 4 rows of supplier marketing tactics from the Google Canvass. The first field [0] in the first row is the supplier name, Awesome Inc.

Copy the Template

The next line in the code will re-create our template and capture the ID of the copied document.

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

Detect that we are using the template ID variable from above. Besides, we are using the DriveApp API. You may need to plough this on in the same way that yous turned on the Sheets API.

This command get-go gets the template doc using the template ID. Adjacent, information technology makes a copy of the template in the aforementioned Drive directory, and finally, information technology gets the ID for the new document so we can use it subsequently.

At the moment, the name of our newly copied document is "Re-create of" any y'all named your template, which is not very helpful, and then we volition change the file'south proper noun.

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

Offset, we get the file using the certificate ID we captured in the previous step. Next, we fix the name. Find that Google Apps Script uses the same fashion of concatenation as JavaScript, and so we can connect strings similar 2018 with variables similar supplier.

Update the Template

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

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

Observe that we are again using the document ID captured when we copied the template, just this fourth dimension nosotros are interacting with the DocumentApp API.

Our first change is to update each case of our supplier name placeholder, ##Supplier##, in the template.

body.replaceText('##Supplier##', supplier)

Observe that replaceText takes two parameters. Outset, in that location is the text we want to replace. Next is the variable representing the supplier name.

The final step is to add together the list of tactics to the marketing proposal. To do this we call a 2d part, parseTactics, passing information technology the header values (i.eastward., "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 nosotros are setting the initial value of the variable i to i rather than 0. This is considering the 0 position in the assortment is the supplier proper name. We want to begin with the i position, which will exist the value of the YouTube Commercial tactic.

for(var i = ane; 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.

JavaScript arrays are zero-based. And so the first detail in the array is in the 0 position. The 2nd item is in the 1 position.

We are using a technique chosen short-excursion 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] != ''

And then we apply the "and" operator, &&. This says that both things must be true. Thus, if the tactics field is empty, it is not true and our adjacent line of code volition not run.

body.appendListItem(headers[i] + ' | ' + tactics[i] + ' internet')

The side by side section of code adds a list particular (like a bulleted list) to the marketing proposal certificate. By default, the API wants to create an ordered listing, as in 1., 2., 3. So next nosotros prepare the list glyph type to BULLET.

.setGlyphType(DocumentApp.GlyphType.BULLET);

Create the Documents

Nosotros accept 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 carte du jour and click "Run."

Be certain that you have the correct function showing in the drop-down menu then click "Run."

Be sure that you have the correct function showing in the drop-downwards carte and so 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.

The new marketing proposals volition 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.go('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 certificate 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);        }  }  part parseTactics(headers, tactics, body){       for(var i = 1; i < tactics.length; i++){     {tactics[i] != '' &&        body.appendListItem(headers[i] + ' | ' + tactics[i] + ' internet').setGlyphType(DocumentApp.GlyphType.BULLET);     }        } }