为Google Sheets/Excel单元格大量指定超链接

Question

我有一个3列的excel文件(名称/时间/链接(。名称是学院名称，时间是文件上传日期，链接是超链接的"test.pdf"；，例如现在，第三列包含6000个应该可以点击的文本单元格，例如，我将把所有文档上传到AWS S3或Google Drive，然后把链接放在单元格中。单元格值和文件名相同。(test.pdf cell=test.pdf可下载文件(。

我想知道的是，在不手动将上传的文件链接复制/粘贴到单元格的情况下，如何将所有这些链接分配给这些单元格？它有6000多条记录，这将花费大量的时间。当然，无论文件是在S3还是GD上，我都可以将它们上传到任何可能的服务中。

TLDR我想将我所有的6000个单元格超链接到人们可以下载的文档，但除了"；手动操作"；。

PS：文件夹结构类似于

学院1文件夹(10个pdf文件(

学院2文件夹(10个pdf文件(

College 600文件夹(10个pdf文档(

例如，如果学院名称是Oxford，则该文件夹中的第一个文档将是P1OU(牛津大学第一期，最多10个文档(

这是电子表格：

https://docs.google.com/spreadsheets/d/1kOWFQSMTYh0RJveDMqTXx_oeL_ueXkeKCBnHmTWk5ZA/edit?usp=sharing

Answer 1

假设

下面的解决方案假设了以下几点：

• 您的文件已经上传到Drive
• 确保电子表格中的文件名与驱动器中的文件名称完全对应
• 在整个驱动器中，没有其他同名文件

应用程序脚本的可能解决方案

1. 打开电子表格。

2. 导航到菜单；工具>quot；脚本编辑器"；。

3. 在脚本编辑器窗口中，将函数替换为：

   function getLinks() {
   var rangeNames = "C2:C10";
   var rangeLinks = "D2:D10";
   var fileNames = SpreadsheetApp.getActiveSheet().getRange(rangeNames).getValues();
   fileNames.forEach(function(row, i) {
   if (row[0] != "") {
   try {
   var fileFound = DriveApp.getFilesByName(row[0]).next();
   fileNames[i]=[fileFound.getDownloadUrl()];
   } catch(e){
   fileNames[i] = ["file not found"];
   };
   };
   });
   SpreadsheetApp.getActiveSheet().getRange(rangeLinks).setValues(fileNames);
   }
   

4. 将变量rangeNames和rangeLinks替换为您希望在其中查看超链接的名称和范围。例如，基于电子表格：

   var rangeNames = "C2:C50";
   var rangeLinks = "D2:D50";
   

警告对于6000行，这可能需要很长的时间，并且可能会超时(最多6分钟(，或者您可能会遇到配额问题(尽管我认为对于6000个请求，这应该没问题(。你可能不得不把它分成几批。点击此处查看更多信息

5. 运行脚本。

6. 如果您希望它是一个具有不同名称并具有超链接的文件。我建议您在拥有所有链接后，在工作表中使用=HYPERLINK函数。

说明

脚本首先获取文件名的范围，并将它们放入一个数组中。然后，它运行一个for循环，该循环将在驱动器中搜索文件名，并返回其下载URL。如果该值为空，它将不会搜索驱动器。如果它找不到值，它将只返回一个"0"；找不到文件"；列中。

参考文献

DriveApp.getFilesByName()

文件迭代器-getFilesByName返回的内容

getDownloadUrl()