Create Google Docs from a Google Sheet

Create Google Docs from a Google Sheet

February 8, 2018 4:00 pm

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.

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 corporate’s Google Sheet accommodates an in depth marketing plan for every of its key suppliers. This instance exhibits solely 4 of them for February 2018.

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

Google Apps Script is an easy scripting language based mostly on JavaScript. In accordance with Google, it permits you to “improve the facility of your favourite Google apps” together with Docs and Sheets.

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.
Associate Google Apps Scripts with your Google Drive.

Affiliate Google Apps Scripts together with your Google Drive.

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.

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.

Making a template is as straightforward as making a Google Doc. Use distinctive placeholders in your template in an effort to substitute them with info from the Google Sheet.

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.

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

Create a brand new Google Apps Script from anyplace in Drive with a proper click on.

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.

You will do your work in the Google Apps Script editor.

You'll do your work within the Google Apps Script editor.

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.
It will be necessary to turn on some APIs, including the Sheets API.

It is going to be essential to activate some APIs, together with the Sheets API.

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.

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.

Yow will discover the ID for any Google Sheet or Doc within the handle bar when that Sheet or Doc is open in your net browser.

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 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 2 between column A and column I, thus “A2:I2.” The values are in row three by way of 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

Google Apps Script doesn't have all the options present in the newest variations of JavaScript. So whereas we'd like to make use of an array perform to work with the info from the Sheet, we'll as an alternative want to make use of a for loop.

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.

Logger.log(techniques);

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][0];

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 [0] 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][0] will level to A4 and the provider, Greatest Firm.

The tactics object in our script is an array of arrays representing the four rows of supplier marketing tactics from the Google Sheet. The first field <em>[0]</em> in the first row is the supplier name, Awesome Inc.

The techniques object in our script is an array of arrays representing the 4 rows of provider advertising techniques from the Google Sheet. The primary subject [0] within the first row is the provider identify, Superior Inc.

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');

First, we get the file utilizing the doc ID we captured within the earlier step. Subsequent, we set the identify. Discover that Google Apps Script makes use of the identical fashion of concatenation as JavaScript, so we will join strings like 2018 with variables like provider.

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.

physique.replaceText('##Provider##', provider)

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[0], 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++)
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 mostly. So the primary merchandise within the array is within the zero place. The second merchandise is within the 1 place.

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.

.setGlyphType(DocumentApp.GlyphType.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.”

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

Be sure that you've the right perform displaying within the drop-down menu then click on “Run.”

It will generate 4 instance advertising proposals in the identical folder as our template.

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

The brand new advertising proposals might be created in the identical folder because the template.

Every of the advertising proposals will embrace the provider’s identify and the techniques the advertising division had in thoughts for them.

The completed marketing proposals are tailored to each supplier and include only the tactics proposed for it.

The finished advertising proposals are tailor-made to every provider and embrace solely the techniques proposed for it.

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][0];
    
    //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[0], 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);
    
    
  

 


You may also like...