将Google Docs URL粘贴到Google Sheet中,自动获取标题和字数

人气:677 发布:2022-10-16 标签: scripting google-docs google-apps-script google-sheets

问题描述

可能不太可能,但有谁知道有什么公式或脚本可以与Google Sheets一起使用,以便当您将选定的URL粘贴到Google Sheets时,它会自动获得每个URL的标题和字数?

推荐答案

您所描述的内容可以使用自定义函数预先实现。它将查询Docs API以获得所需的元数据,并将其返回到单元格中。

但是,这是不可能的,因为不允许从自定义函数(1)访问DocumentApp等API。在尝试执行此操作时,它们将返回如下错误:

您无权调用DocumentApp.OpenByUrl。

所需权限:https://www.googleapis.com/auth/documents(第88行)。

替代方案

在整个Sheets文档中随意插入=GETDOCWORDCOUNT()=GETDOCTITLE()函数。首先,它们将显示#ERROR!作为函数的结果,原因如上所述:

该代码在打开Sheet文档时,会创建一个"自定义脚本"菜单。将公式放入文档后,单击菜单并根据需要选择Run GETDOCWORDCOUNTRun GETDOCTITLE。工作表中以前显示为#ERROR!的公式将替换为运行函数的结果。在此步骤中,执行GETDOCWORDCOUNTGETDOCTITLE函数时发现的任何错误也将显示给用户:

代码

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

演示

查看脚本演示短片here

777