Create Google Docs from a Google Sheet

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 what you are promoting 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 slicing and pasting) knowledge from the Google Sheet to every of one hundred Google Docs manually, a really time-consuming process.

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. Based on Google, it permits you to “improve the facility of your favourite Google apps” together with Docs and Sheets.

In apply, 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 is advisable 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 area 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.

—

Once 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 option to discover this ID is to look within the handle bar in your net browser once 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 by means 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 could have 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’ll need 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 shall 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.

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 approach 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 intervening time, 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 type 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 substitute. Subsequent is the variable representing the provider identify.

The ultimate step is so as to add the listing 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++)

 

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 verify to see if the tactic has a worth. Particularly, we’re asking if this “tactic just isn’t 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 subject 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 listing, as in 1., 2., three. So subsequent we set the listing glyph sort to BULLET.

.setGlyphType(DocumentApp.GlyphType.BULLET);

 

Create the Paperwork

We now have 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][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);