Tag: Google Docs

Code

Delete Blank Rows from Tables in a Google Document with Apps Script

Post author By Amit Agarwal
Post date 2017-06-15

This Google App Script snippet will help you quickly delete all the blank rows of any tables inside your Google Document.

You can either pass the Google Drive File ID to the removeBlankRows() method or it will take the currently active Google Document as input.


/* 
*  Written by Amit Agarwal
*  Email: amit@labnol.org
*
*/

function removeBlankRows(docId) {

    var document = docId ?
        DocumentApp.openById(docId) :
        DocumentApp.getActiveDocument();

    var body = document.getBody();
    var search = null;
    var tables = [];

    // Extract all the tables inside the Google Document
    while (search = body.findElement(DocumentApp.ElementType.TABLE, search)) {
        tables.push(search.getElement().asTable());
    }

    tables.forEach(function (table) {
        var rows = table.getNumRows();
        // Iterate through each row of the table
        for (var r = rows - 1; r >= 0; r--) {
            // If the table row contains no text, delete it
            if (table.getRow(r).getText().replace(/\s/g, "") === "") {
                table.removeRow(r);
            }
        }
    });

    document.saveAndClose();
}

Tags Google Apps Script, Google Docs

Code

Count Number of Pages in PDF with Google Script

Post author By Amit Agarwal
Post date 2015-11-05

The Google Documents API doesn’t offer a method to get the number of pages in a document. Dave Lam has an interesting workaround that will not only help you count the pages in a Google Document but of any PDF file that’s in your Google Drive.

The idea is to convert the Google Document into PDF and then parse the content of the PDF document as a text file using Regular Expressions.


/* Credit: https://code.google.com/p/google-apps-script-issues/issues/detail?id=1656 */

function countPDFPages() {

  var blob = DocumentApp.getActiveDocument().getAs("application/pdf");
  var data = blob.getDataAsString();

  var re = /Pages\/Count (\d+)/g;
  var match;
  var pages = 0;

  while (match = re.exec(data)) {

    var value = parseInt(match[1]);

    if (value > pages) {
      pages = value;
    }

  }

  Logger.log("Total PDF pages = " + pages);

  return pages;
}

Tags Google Apps Script, Google Docs, PDF

Code

Save Gmail Messages as Google Documents

Post author By Amit Agarwal
Post date 2015-05-22

The Google Script will save any Gmail message (or thread) in your Google Drive as a native Google Document with proper formatting. Unlike the Save Gmail as PDF script that downloads the email threads as PDF files in your Google Drive, this Google Script create a Google Docs file for your Gmail message and these do not count against the storage quota.


function saveGmail(msgID) {
    
  // Based on Drive Scoop
  // Available at https://github.com/google/gfw-deployments

  var message = GmailApp.getMessageById(msgID);
  
  // Grab the message's headers.
  var from = message.getFrom();
  var subject = message.getSubject();
  var to = message.getTo();
  var cc = message.getCc();
  var date = message.getDate();
  var body = message.getBody();
  
  // Begin creating a doc.
  var document = DocumentApp.create(subject);  
  var document_title = document.appendParagraph(subject);  
  document_title.setHeading(DocumentApp.ParagraphHeading.HEADING1);
  
  var style = {};
  style[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = (DocumentApp.HorizontalAlignment.CENTER);
  document_title.setAttributes(style);
  
  var headers_heading = (document.appendParagraph("Gmail Message Headers"));
  headers_heading.setHeading(DocumentApp.ParagraphHeading.HEADING2);
  
  AddGmailHeaderToDoc(document, "From", from);
  AddGmailHeaderToDoc(document, "To", to);
  AddGmailHeaderToDoc(document, "Cc", cc);
  AddGmailHeaderToDoc(document, "Date", date);
  AddGmailHeaderToDoc(document, "Subject", subject);
  
  var body_heading = (
    document.appendParagraph("Body (without Markup)"));
  body_heading.setHeading(DocumentApp.ParagraphHeading.HEADING2);
  
  var sanitized_body = body.replace(/<\/div>/, "\r\r");
  sanitized_body = sanitized_body.replace(//g, "\r");
  sanitized_body = sanitized_body.replace(/<\/p>/g, "\r\r");
  sanitized_body = sanitized_body.replace(/<.*?>/g, "");
  sanitized_body = sanitized_body.replace(/'/g, "'");
  sanitized_body = sanitized_body.replace(/"/g, '"');
  sanitized_body = sanitized_body.replace(/&/g, "&");
  sanitized_body = sanitized_body.replace(/\r\r\r/g, "\r\r");
  
  var paragraph = document.appendParagraph(sanitized_body);
  
  document.saveAndClose();
  
  return document.getUrl();
  
}

function AddGmailHeaderToDoc(document, header_name, header_value) {
  if (header_value === "") return;
  var paragraph = document.appendParagraph("");
  paragraph.setIndentStart(72.0);
  paragraph.setIndentFirstLine(36.0);
  paragraph.setSpacingBefore(0.0);
  paragraph.setSpacingAfter(0.0);    
  var name = paragraph.appendText(header_name + ": ");
  name.setBold(false);
  var value = paragraph.appendText(header_value);
  value.setBold(true);
}

Tags Gmail, Google Apps Script, Google Docs

Code

How to Merge Multiple Google Documents

Post author By Amit Agarwal
Post date 2015-02-11

You can use Google Apps Script to merge two or more Google Documents into a single document. The script takes the first document and appends the content of all the other documents into this document. All the formatting, tables, lists and other elements are preserved in the merged document.


/* Snippet from http://stackoverflow.com/questions/10692669 */

function mergeGoogleDocs() {

  var docIDs = ['documentID_1','documentID_2','documentID_3','documentID_4'];
  var baseDoc = DocumentApp.openById(docIDs[0]);

  var body = baseDoc.getActiveSection();

  for (var i = 1; i < docIDs.length; ++i ) {
    var otherBody = DocumentApp.openById(docIDs[i]).getActiveSection();
    var totalElements = otherBody.getNumChildren();
    for( var j = 0; j < totalElements; ++j ) {
      var element = otherBody.getChild(j).copy();
      var type = element.getType();
      if( type == DocumentApp.ElementType.PARAGRAPH )
        body.appendParagraph(element);
      else if( type == DocumentApp.ElementType.TABLE )
        body.appendTable(element);
      else if( type == DocumentApp.ElementType.LIST_ITEM )
        body.appendListItem(element);
      else
        throw new Error("Unknown element type: "+type);
    }
  }
}

Update: [Merijn Peeters] My document included a very big table, and when merging several of those documents, a blank line was added from the second page onward. This corrupted the layout, of course.

After hours of searching, I discovered that the error was due to the fact that the 'appendTable' function automatically appends a blank paragraph as well, because a document cannot end with a table. From Google's documentation:

appendTable() - Creates and appends a new Table - This method will also append an empty paragraph after the table, since Google Docs documents cannot end with a table.

Tags Google Apps Script, Google Docs

Code

Universal Find and Replace for Google Drive Documents

Post author By Amit Agarwal
Post date 2014-12-01

Say you have a folder of invoice in your Google Drive and you would like to change your company name in all these documents from “ABC Inc.” to “XYZ Inc.”. You can use Apps Script to find a string in multiple documents in a specific folder and replace it with another string. You can even use regular expressions to perform the replacement.

The getDriveFolder() method is a custom function used to get a Drive folder with a specific folder path. The script retrieves all the Google Documents (with a particular MIME type) and performs Universal find and replace.


// Universal Find and Replace
function universalFindAndReplace() {
  
  var folder = getDriveFolder("a/b/c/d/e/f");
  
  var backup = folder.createFolder("Backup - " + (new Date()).getTime());
  
  var doc, documents = folder.getFilesByType(MimeType.GOOGLE_DOCS)
  
  while (documents.hasNext()) {
    
    var file = documents.next();
    
    file.makeCopy(file.getName(), backup);
    
    var doc = DocumentApp.openById(file.getId());
    
    var body = doc.getBody();
    
    // You can use regex too
    doc.replaceText("brown", "black"); 
    
    doc.saveAndClose();
  
  }
  
}

Tags Google Apps Script, Google Docs

Code

Send Tweets from Google Sheets

Post author By Amit Agarwal
Post date 2014-05-10

It is possible to send bulk tweets from Google Spreadsheet using Google Scripts. To get started, you need to include the OAuth1 libarary in your project, create a new Twitter app on apps.twitter.com and pass the Consumer Keys and API Secret to the Google Script project.

function sendTweet(user, tweet, tweet_type) {

    var twitterService = getTwitterService_();

    // If the Google Apps user has authorized the Twitter service
    if (twitterService.hasAccess()) {

        // Remove @ from the Twitter user name, if found
        var twitterUser = user.trim().replace(/^\@/, "");

        var api = "https://api.twitter.com/1.1/";

        // Send a public @tweet or direct message (DM)
        if (tweet_type === "DM") {
            api += "direct_messages/new.json?screen_name=" + twitterUser + "&text=" + encodeString_(tweet);
        } else if (tweet_type === "TWEET") {
            tweet = "@" + twitterUser + " " + tweet;
            api = "statuses/update.json?status=" + encodeString_(tweet);
        }

        var response = twitterService.fetch(api, {
            method: "POST",
            muteHttpExceptions: true
        });
        if (response.getResponseCode() === 200) {
            Logger.log("Tweet sent");
        } else {
            Logger.log("ERROR: " + JSON.parse(response.getContentText()).errors[0].message);
        }
    }
}

// Google Script has trouble sending tweets that contain !*()'
// so we replace these variable from the status text
function encodeString_(q) {
    var str = q;
    str = str.replace(/!/g, 'Ị');
    str = str.replace(/\*/g, '×');
    str = str.replace(/\(/g, '[');
    str = str.replace(/\)/g, ']');
    str = str.replace(/'/g, '’');
    return encodeURIComponent(str);
}

Tags Google Apps Script, Google Docs, Twitter

Code

Set Sharing Permissions in Google Drive with Apps Script

Post author By Amit Agarwal
Post date 2014-05-01

You can easily change the sharing and access permissions of any shared file or folder in Google Drive with the help of Google Apps Script.

The following Google Script sets auto-expiration dates for shared links and makes the folder /file “Private” after the expiration date.

var EXPIRY_TIME  = "2014-05-01 23:42"; 

function autoExpire() {
  
  var id, asset, i, email, users;

  // The URL of the Google Drive file or folder 
  var URL = "https://drive.google.com/folderview?id=0B4fk8L6brI_ednJaa052";
  
  try {
    
    // Extract the File or Folder ID from the Drive URL
    var id = URL.match(/[-\w]{25,}/);
    
    if (id) {
      
      asset = DriveApp.getFileById(id) ? DriveApp.getFileById(id) : DriveApp.getFolderById(id);
      
      if (asset) {
        
        // Make the folder / file Private 
        asset.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.NONE);  
        asset.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.NONE); 
        
        // Remove all users who have edit permissions
        users = asset.getEditors();
        for (i in users) {
          email = users[i].getEmail();
          if (email != "") {
            asset.removeEditor(email);
          }
        }
        
        // Remove all users who have view permssions
        users = asset.getViewers();
        for (i in users) {
          email = users[i].getEmail();
          if (email != "") {
            asset.removeViewer(email);
          }
        }  
        
      }
    }
  
  } catch (e) {
    
    Logger.log(e.toString());
    
  }
}

function Start() {
  
  var triggers = ScriptApp.getProjectTriggers();
  
  for (var i in triggers) {
    
    ScriptApp.deleteTrigger(triggers[i]);
    
  }
  
  var time = EXPIRY_TIME;
  
  // Run the auto-expiry script at this date and time

  var expireAt = new Date(time.substr(0,4),
                          time.substr(5,2)-1,
                          time.substr(8,2),
                          time.substr(11,2),
                          time.substr(14,2));
  
  if ( !isNaN ( expireAt.getTime() ) ) {
    ScriptApp.newTrigger("autoExpire").timeBased().at(expireAt).create();
  }

}

Tags Google Apps Script, Google Docs, Google Drive

Code

Screen Sraping the Google Play Store

Post author By Amit Agarwal
Post date 2013-09-01

I was trying to fetch the average ratings and the download count of the top Android Apps from the Google Play Store but since they offer no API, screen scraping was the only solution.

As a first step, I wrote a Google search query that returns all Android apps that have been downloaded 500+ million times from the Play Store. The query looked something like this:

site:play.google.com "500,000,000 - 1,000,000,000"

The parameter num=100 was appended to the Google Search URL so that it returns 100 search results on the first page. I then created a new sheet in Google Docs and used the ImportXML function to extract all the Google Play hyperlinks into the Google Sheet (A1 is the Google URL).

=importXML(A1, "//h3/a/@href")

Once the Google Play URL of an App is known, the rating and count can be easily know using another importXML function (K3 is the Google Play url for any Android App).

=importXML(K3,"//meta[@itemprop='ratingValue']/@content")
=importXML(K3,"//meta[@itemprop='ratingCount']/@content")

Please note that a Google Sheet can have a maximum of 50 ImportXML functions.

Tags Google Docs

Code

Google Script for Extracting Email Addresses

Post author By Amit Agarwal
Post date 2013-06-14

This Google Apps Script will sift through your Gmail account and extract email addresses of senders which are then saved in a Google Sheet. Useful for email marketing and mail merge.

 
/** This script will extract email address from your Gmail mailbox **/
/**   Written by Amit Agarwal on 06/13/2013    **/

function extractEmailAddresses() {
   
  var ss = SpreadsheetApp.getActiveSpreadsheet();   
  var sheet = ss.getSheets()[0];
  
  var monitor = sheet.getRange("A2").getValue();
  var processed = sheet.getRange("B2").getValue();
  
  var label = GmailApp.getUserLabelByName(processed);         
  var search = "in:" + monitor + " -in:" + processed;

  // Process 50 Gmail threads in a batch to prevent script execution errors
  var threads = GmailApp.search(search, 0, 50);      
  
  var row, messages, from, email;
  
  try {
    
    for (var x=0; x", "");
        email = email.replace("<", "");
        
        row   = sheet.getLastRow() + 1;        
        // If an email address if found, add it to the sheet
        sheet.getRange(row,1).setValue(email);
      }
      
      threads[x].addLabel(label);
      
    }
    
  }

  catch (e) {
    Logger.log(e.toString());
    Utilities.sleep(5000);
  }
    
  // All messages in the label have been processed?
  if ( threads.length === 0 ) {
    GmailApp.sendEmail(Session.getActiveUser().getEmail(), "Extraction Done", 
                       "Download the sheet from " + ss.getUrl());
  }  
}


// Remove Duplicate Email addresses
function cleanList() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange(4, 1, sheet.getLastRow()).getValues();
  var newData = new Array();
  for(i in data){
    var row = data[i];
    var duplicate = false;
    for(j in newData){
      if(row[0] == newData[j][0]){
        duplicate = true;
      }
    }
    if(!duplicate){
      newData.push(row);
    }
  }

  // Put the unique email addresses in the Google sheet
  sheet.getRange(4, 2, newData.length, newData[0].length).setValues(newData);
}

Tags Gmail, Google Apps Script, Google Docs