3 Impressive Google Docs Scripts to Automate Your Documents

There are many good reasons you should be using cloud-based Google Docs rather than application-based word processing apps like Microsoft Word. One of the most important is that you can create some very useful automations using powerful Google Scripts.

Here are three scripts that let you build a document from user prompts, import Google Analytics into a Google Doc report, and create a document from a Google Sheet file.

1. Use Prompts to Build a Document

If you send out the same email often, an automated document template can really help you save time. This could be something like a monthly sales report to your manager, or weekly updates to colleagues. Anything you write up often can be automated with a document template and a Google Script.

The first step is creating a document template. Doing this is as simple as creating a Google Doc. For the words you’d like to fill in, just surround them with two ## signs, like the example below.

Inside this document, you can create a Google Script that runs when the document opens. Your script will prompt you to enter each of the elements that go into the document.

To create your script, click on the Tools menu item, and click on Script editor.

In the editor window, delete the code that’s there and replace it with the following script.

This script will call up prompts when the document opens, create a new document with the name of the sales person and today’s date in the title. Then it’ll fill in the template fields with the information you’ve entered.

function myFunction() {
  // Display a dialog box for each field you need information for.
  
  var ui = DocumentApp.getUi();
  //var response = ui.prompt('Enter Name', 'Enter sales person's name', ui.ButtonSet.OK);
  var nameResponse = ui.prompt('Enter sales persons name');
  var client1Response = ui.prompt('Enter client 1');
  var sales1Response = ui.prompt('Enter sales 1');
  var client2Response = ui.prompt('Enter client 2');
  var sales2Response = ui.prompt('Enter sales 2');
  var client3Response = ui.prompt('Enter client 3');
  var sales3Response = ui.prompt('Enter sales 3');
  var commissionResponse = ui.prompt('Enter commission');
  var date = new Date();
    
  //Make a copy of the template file
  var documentId = DriveApp.getFileById('<your-template-id>').makeCopy().getId();
      
  //Rename the copied file
  DriveApp.getFileById(documentId).setName(nameResponse.getResponseText() + date + ' Sales Report');  
      
  //Get the document body as a variable
  var body = DocumentApp.openById(documentId).getBody();
    
  //Insert the entries into the document
  body.replaceText('##name##', nameResponse.getResponseText());
  body.replaceText('##client1##', client1Response.getResponseText());
  body.replaceText('##sales1##', sales1Response.getResponseText()); 
  body.replaceText('##client2##', client2Response.getResponseText());
  body.replaceText('##sales2##', sales2Response.getResponseText());  
  body.replaceText('##client3##', client3Response.getResponseText());
  body.replaceText('##sales3##', sales3Response.getResponseText());
  body.replaceText('##commission##', commissionResponse.getResponseText());   
}

Replace the document ID code in the script above with the document ID of the template document.

You’ll find this embedded in the URL when you’re editing the template document.

In the Google Script editor window, click the disk icon to save the script.

Next, click on the run icon to test that it works.

When you run the script for the first time, you may need to approve permissions for your Google Account to run the script.

Back in the template document, you’ll see the prompt windows pop up one after the other. Fill in the fields with the data you want to go into the document.

When you’re done, the script will create a new document in your Google Drive root folder with all of the information you’ve entered filled in.

Finally, you just need to set up the script to run every time you open the template document.

In the Google Script Editor window, click on Edit > Current project’s triggers.

Click on the Add Trigger button in the lower right corner. Make sure Select event type is On open.

Scroll down and click Save, and you’re done.

2. Import Google Analytics Into a Document Report

Whether you own a website, or you work for someone who owns one, a frequent requirement is to measure website traffic and performance and provide it inside a formatted report.

You can use the same template approach as the section above to export website data from Google Analytics and output it to a well-formatted Google Docs report.

First, create a report template just like you did in the previous section. In this example the template will be set up to provide total users, sessions, and pageviews for the past week.

Next, go into the Google Docs Script Editor using the same steps as the previous section.

Enable access to Google Analytics data by following the steps below.

1. In the script editor window, select Resources, and then Advanced Google services
2. Click on/off next to Google Analytics API
3. Below the dialog, click the Google Cloud Platform API Dashboard link
4. In the Cloud Platform window, click on Enable APIs and Services
5. Search for Analytics, and click on Analytics API
6. Click on the Enable button to enable this API for your script
7. Go back to the script editor window and click OK to close the Advanced Google Services window

Now that you’ve enabled integration with the Google Analytics API, you’re ready to automatically create your report.

Paste the following code inside the script editor code window.

function myFunction() {
  var tableId = 'ga:<your-analytics-id>';
  var startDate = getLastNdays(7);   // 1 week ago.
  var endDate = getLastNdays(0);
  var date = new Date();
  
  var results = Analytics.Data.Ga.get(
      tableId,
      startDate,
      endDate,
    'ga:users,ga:sessions,ga:pageviews',
      {'dimensions': 'ga:date'});

  var data = [];
  var totals = results.totalsForAllResults;
    for (metricName in totals) {
      data.push(totals[metricName]);
  }
  
  var users = data[0]

  var sessions = data[1]

  var pageviews = data[2]
  
  // Output to Google Doc.

  //Make a copy of the template file
  var documentId = DriveApp.getFileById('').makeCopy().getId();
  
  //Rename the copied file
  DriveApp.getFileById(documentId).setName(date + ' Website Report');
  
  //Get the document body as a variable
  var body = DocumentApp.openById(documentId).getBody();
  
  //Insert the entries into the document
  body.replaceText('##startdate##', startDate);
  body.replaceText('##enddate##', endDate);
  body.replaceText('##users##', users); 
  body.replaceText('##sessions##', sessions);
  body.replaceText('##pageviews##', pageviews);
}

function getLastNdays(nDaysAgo) {
  var today = new Date();
  var before = new Date();
  before.setDate(today.getDate() - nDaysAgo);
  return Utilities.formatDate(before, 'GMT', 'yyyy-MM-dd');
}

Replace the document ID code in the script above with the document ID of the template document. Also replace the Analytics ID with the ID displayed for your website in Google Analytics.

In the Google Script editor window, click the disk icon to save the script.

Click on the run icon to test that it works.

When you run the script for the first time, you’ll have to approve permissions for your Google Account to run the script.

Running the script will create a new document in your Google Drive root folder with all of the website performance information for the last week filled in.

Finally, you just need to set up the script to run every week.

In the Google Script Editor window, click on Edit and Current project’s triggers.

Click on the Add Trigger button in the lower right corner. Change Select event source to Time-driven. Change Select type of time based trigger to Week timer.

Scroll down and click Save, and your script will run every week and create a new weekly report.

3. Create a Document From a Google Sheet

There are times when you need to transfer information from a spreadsheet into a document, for things like developing reports or logging information.

If you find yourself doing this frequently, you can save time by integrating Google Sheets with Google Docs. For this script, you’re going to use the Google Scripts Editor inside of Google Sheets, since that’s where the data will come from.

First, you’re going to create your template sales document just as you did in the first section of this article, embedding variables surrounded by the ## symbol.

The data for these reports will come from your Google Sheets spreadsheet, which may look something like this example.

To create your script, inside of Google Sheets, click on the Tools menu item, and click on Script editor.

Paste the following script in the editor script window.

function myFunction() {
  var date = new Date();

  // Opens SS by its ID
  var ss = SpreadsheetApp.openById("<spreadsheet-document-id>");

  var sheet = ss.getSheetByName('Sam'); // or whatever is the name of the sheet 

  //Make a copy of the template file
  var documentId = DriveApp.getFileById('<template-document-id>').makeCopy().getId();
  
  //Rename the copied file
  DriveApp.getFileById(documentId).setName('Sam ' + date + ' Sales Report');  
  
  //Get the document body as a variable
  var body = DocumentApp.openById(documentId).getBody();

  var range = sheet.getRange("A2:B4");

  body.replaceText('##name##', 'Sam');

  for (var i=1; i<4; i++) {
    cell = range.getCell(i,1);
    client = cell.getValue(); 
    body.replaceText('##client' + i + '##', client);
    cell = range.getCell(i,2);
    sales = cell.getValue();
    body.replaceText('##sales' + i + '##', sales);    
  }
}

Replace the document ID in the script above with the document ID of the template document. Also replace the Google Sheet ID with the ID of your data Google Sheet.

In the Google Script editor window, click the disk icon to save the script.

Click on the run icon to test that it works.

Remember, when you run the script for the first time, you’ll need to approve permissions so it can access your Google Account.

When you run the script, it’ll create a new document in your Google Drive root folder with all of the sales data from the spreadsheet.

It’s also possible to cycle through the sheet tabs and create a new sales output document for every sales person on your team.

Why not play around with the script above and see if you can pull this off?

Using Google Scripts and Google Docs

Google Docs seems like a simple word processor, but as you can see when you integrate Google Analytics, it multiplies the functionality of this cloud app.

Templates in Google Docs are a core part of what makes all of this work. You’d be surprised just how much using templates in Google Docs can make everything you do much more efficient. Try them out, and while you’re at it add your own creative Google Script automations as well.

Read the full article: 3 Impressive Google Docs Scripts to Automate Your Documents

from MakeUseOf https://ift.tt/2PE2six