Paste Google Docs URLs into Google Sheets and automatically get title and word count

Question

Probably a long shot, but does anyone know of any formulae or scripts that could be used with Google Sheets so that when you paste in a selection of URLs to Google sheets, it would automatically get the title and word count for each URL?

Answer 1

What you describe could, a priori, be implemented using custom functions. It would query the Docs API for the desired metadata and return it into the cell.

This, however, is not possible since access to apis such as DocumentApp is not allowed from custom functions (1). Upon attempting to do so, they will return an error such as the following:

You do not have permission to call DocumentApp.openByUrl.

Required permissions: https://www.googleapis.com/auth/documents (line 88).

Alternative

1. Insert the =GETDOCWORDCOUNT() and =GETDOCTITLE() functions as you please throughout your Sheets document. At first, they will show #ERROR! as result of the function, for the reason explained above:

   

2. The code, when opening the Sheets document, creates a "Custom scripts" menu. After having placed your formulas in the document, click on the menu and select the Run GETDOCWORDCOUNT or Run GETDOCTITLE as you wish. The formulas in the Sheet that were previously shown as #ERROR! will be replaced by the result of running the functions. At this step, any error found whilst executing the GETDOCWORDCOUNT, GETDOCTITLE functions will also be shown to the user:

   

Code

var GETDOCWORDCOUNT_FUNCTION_REGEX = /=GETDOCWORDCOUNT\((.+)\)/;
var GETDOCTITLE_FUNCTION_REGEX = /=GETDOCTITLE\((.+)\)/;
var A1_CELL_REGEX = /^[A-Z]+[1-9][0-9]*$/;

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu("Custom scripts")
    .addItem("Run GETDOCWORDCOUNT", "runGetDocWordCount")
    .addItem("Run GETDOCTITLE", "runGetDocTitle")
    .addToUi();
}

function runGetDocWordCount() {
  var sh = SpreadsheetApp.getActiveSheet();

  var finder = sh.createTextFinder("\=GETDOCWORDCOUNT\(.+)")
                 .matchCase(true)
                 .matchEntireCell(true)
                 .useRegularExpression(true)
                 .matchFormulaText(true);

  var results = finder.findAll();
  var errors = [];

  for (var i=0; i<results.length; i++) {
    var range = results[i];
    var formula = range.getFormula();

    var cell = formula.match(GETDOCWORDCOUNT_FUNCTION_REGEX)[1];
    var url = sh.getRange(cell).getValue();
    try {
      range.setValue(GETDOCWORDCOUNT(url));
    } catch(e) {
      errors.push(range.getA1Notation() + ': ' + e.toString());
    }
  }

  if (errors.length > 0) {
    var ui = SpreadsheetApp.getUi();
    var title = errors.length.toString() + ' errors found';
    var prompt = errors.join('\n');
    ui.alert(errors.length + ' errors found', prompt, ui.ButtonSet.OK);
  }
}

function runGetDocTitle() {
  var sh = SpreadsheetApp.getActiveSheet();

  var finder = sh.createTextFinder("\=GETDOCTITLE\(.+)")
                 .matchCase(true)
                 .matchEntireCell(true)
                 .useRegularExpression(true)
                 .matchFormulaText(true);

  var results = finder.findAll();
  var errors = [];

  for (var i=0; i<results.length; i++) {
    var range = results[i];
    var formula = range.getFormula();

    var cell = formula.match(GETDOCTITLE_FUNCTION_REGEX)[1];
    var url = sh.getRange(cell).getValue();
    try {
      range.setValue(GETDOCTITLE(url));
    } catch(e) {
      errors.push(range.getA1Notation() + ': ' + e.toString());
    }
  }

  if (errors.length > 0) {
    var ui = SpreadsheetApp.getUi();
    var title = errors.length.toString() + ' errors found';
    var prompt = errors.join('\n');
    ui.alert(errors.length + ' errors found', prompt, ui.ButtonSet.OK);
  }
}

function GETDOCWORDCOUNT(url) {
  var doc = DocumentApp.openByUrl(url);
  var text = doc.getBody().getText();
  var words = text.split(/\S+/).length;
  return words;
}

function GETDOCTITLE(url) {
  var doc = DocumentApp.openByUrl(url);
  return doc.getName();
}

function isA1Cell(val) {
  if (typeof val != "string") return false;

  return A1_CELL_REGEX.test(val);
}

Demo

See a short video demo of the script here