我有一个包含多个表单的电子表格文件。每个表单都是一份报告并且拥有不同的链接。如何获取当前活动表单的链接?
例如:输出 > 报告的链接可在这里找到。
祝你好运!
var thisDocumentUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl();
您的“这里”链接无法使用,但是,您可以创建一个公共超链接到特定的表格。
当您在浏览器中打开Google Sheet并导航工作表时,您会注意到URL中有一个锚元素#gid=some_number。
将相同的#gid=some_number部分附加到电子表格的任何超链接上,即可直接链接到该工作表。
如果您正在使用脚本以编程方式生成工作表,因此不知道gid,则需要在您的Google Script中使用getSheetID函数。
如果是您的根目录,可能需要一些时间。
function getFnFX(folder = DriveApp.getRootFolder()) {
var tree = JSON.parse(PropertiesService.getScriptProperties().getProperty('FnF'));
//Logger.log(JSON.stringify(tree));
if (tree.level < level) {
tree.level = level;
PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
}
const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
let row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = folder.getName(); } else { x = ''; } return x; }));
tree.txt.push(row);
row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = 'bold'; } else { x = 'normal'; } return x; }));
tree.fwt.push(row);
PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
if (files.hasNext()) {
let row = Array.from([...Array(level).keys()], ((x, i) => { if (i == level - 1) { x = 'Files:'; } else { x = ''; } return x; }));
tree.txt.push(row);
tree.fwt.push(['normal']);
PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
}
while (files.hasNext()) {
let file = files.next();
let row = Array.from([...Array(level + 1).keys()], ((x, i) => { if (i == level) { x = file.getName(); } else { x = ''; } return x; }));
tree.txt.push(row);
//The next section of the code is hard wired for an array of 4 columns because I knew it would work with a folder that has two levels of subfolders so it won't work for my whole drive. I did that just for simplicity for this example.
SpreadsheetApp.openById(file.getId()).getSheets().forEach(s =>
tree.txt.push(["", "", "", `=HYPERLINK("https://docs.google.com/spreadsheets/d/${file.getId()}/edit#gid=${s.getSheetId()}","${s.getName()}")`]));
tree.fwt.push(['normal']);
PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify(tree));
}
const subfolders = folder.getFolders()
while (subfolders.hasNext()) {
let subfolder = subfolders.next();
level++;
getFnFX(subfolder);
}
level--;
}
function getFilesAndFoldersX() {
const fldr = null;
const ss = SpreadsheetApp.getActive();
ss.toast("Entry");
const sh = ss.getSheetByName('Sheet1');
sh.clearContents();
SpreadsheetApp.flush();
PropertiesService.getScriptProperties().setProperty('FnF', JSON.stringify({ txt: [], fwt: [], level: 0 }));
getFnFX();//if you have no parameters here it will start with the root and recursive access every folder in your drive. If the provide it with DriveApp.getFolderById() then it will limit itself to that folder
//Logger.log(PropertiesService.getScriptProperties().getProperty('FnF'));
let tree = JSON.parse(PropertiesService.getScriptProperties().getProperty('FnF'));
const l = tree.level + 1
tree.txt.forEach(r => {
if (r.length < l) {
//Array.from(Array(l - r.length).keys()).forEach(e => r.push(''));
r.splice(r.length, 0, ...Array(l - r.length).fill(''));
}
});
tree.fwt.forEach(r => {
if (r.length < l) {
//Array.from(Array(l - r.length).keys()).forEach(e => r.push('normal'));
r.splice(r.length, 0, ...Array(l - r.length).fill('normal'));
}
});
Logger.log(JSON.stringify(tree));
sh.getRange(1, 1, tree.txt.length, tree.level + 1).setValues(tree.txt);
sh.getRange(1, 1, tree.fwt.length, tree.level + 1).setFontWeights(tree.fwt);
PropertiesService.getScriptProperties().deleteProperty('FnF');
ss.toast("EOF");
}
我只对一个文件夹进行了解锁,该文件夹只有几个层级,以下是结果:
根目录 | 一级目录 | 二级目录 | 链接 |
---|---|---|---|
测试文件夹 | |||
文件: | |||
测试目标 | |||
表格1 | |||
表格0 | |||
测试源 | |||
表格1 | |||
选项 | |||
表格2 | |||
表格4 | |||
表格3 | |||
测试 doPost 和 doGet | |||
表格1 | |||
LIZ | |||
JJE-数据生成器 | |||
表格1 | |||
表格2 | |||
文件夹3 | |||
文件夹2 | |||
文件夹4 | |||
文件夹1 |