问题描述
可能不太可能,但有谁知道有什么公式或脚本可以与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 GETDOCWORDCOUNT
或Run GETDOCTITLE
。工作表中以前显示为#ERROR!
的公式将替换为运行函数的结果。在此步骤中,执行GETDOCWORDCOUNT
、GETDOCTITLE
函数时发现的任何错误也将显示给用户:
代码
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