google-apps-script - 如何检索可共享链接的列表？

Question

对于以下内容，所有文件均采用Google Sheet 格式。

我的问题： 我的工作环境结构如下（文件夹/子文件夹）：

拉辛：
--> ID4522
--> ID7852
--> ID5864
--> ....

跟踪文件“FOLLOW_UP”位于 RACINE 文件夹中。ID4522、ID7852 和 ID5864 文件夹是 RACINE 文件夹的子文件夹。

我们在 ID4522 子文件夹中找到以下 Google 表格文件“Entry_Form_ID4522”，在 ID7852 子文件夹中找到以下 Google 表格文件“Entry_Form_ID7852”，...</p>

重要说明：子文件（“IDxxxx”形式）的数量可以随时变化而不会发出警告。

我的希望

很可能通过 javascript 中的宏，在单元格 B3 及以下的跟踪文件（“FOLLOW_UP”）中检索每个文件“Entry_Form_IDxxxx”的可共享链接列表。子文件夹列表可以随时更改（例如，当我的客户添加具有关联的“Entry_Form_IDxxxx”文件的文件夹时）。

提前谢谢你。

问候。杰罗姆

标签： google-apps-script

Answer 1

文件 URL 的递归文件夹

我假设您只有一个名为 RACINE 的文件夹，并且其中只有一个名为“FOLLOW UP”的文件，并且您的数据所在的工作表是最左边的工作表。假设这是真的，那么这个函数将从第 3 行到工作表末尾的 B 列中找到的 =HYPERLINK() 单元格中提取 url。

最初我得出的结论是，我们在 B 列中有链接，并且您想要一个 URL 列表，所以这个函数可能不是您想要的。请告诉我，我会删除它。

function getLinks() {
  const folder=DriveApp.getFoldersByName("RACINE").next();//assumes only one folder with that name
  const file=folder.getFilesByName("FOLLOW UP").next();//assumes only one file by that name
  const ss=SpreadsheetApp.openById(file.getId());
  const sh=ss.getSheets()[0];//the most left sheet in the spreadsheet
  const vs=sh.getRange(3,2,sh.getLastRow()-2,1).getFormulas();
  var links=[];
  vs.forEach(function(r,i){
    let v=r[0].match(/([^"]+)/g);
    links.push(v[1]);  
  })
  var ui=HtmlService.createHtmlOutput(links.join('<br />'));
  SpreadsheetApp.getUi().showModelessDialog(ui, "URLS");
  return links;//as an array
}

下结论后

我认为这是您真正想要的，这是您的工作子目录中满足特定命名格式的文件的 url 列表，如以下段落中所述。我在这个答案中用最后两个函数生成的一些数据上对此进行了测试。在修复了几个错别字之后，它运行得非常好，并且在正确的位置生成了 url。

第一个功能基本上是启动器。它假定您的 google 驱动器上只有一个名为“RACINE”的文件夹。它调用一个递归函数，该函数本质上是爬过所有 RACINE 的子文件夹，寻找“Entry_Form_IDxxxx”形式的文件，其中 xxxx 都是 0-9 之间的数字。当它找到这样的文件名时，它会将该 URL 加载到“FOLLOW UP”的工作表 [0] 中 columnB 底部的下一个空单元格中。它还搜索“IDxxxx”形式的子目录，其中 xxxx 是 0-9 之间的所有数字。当它通过从 getFnF() 内部调用 getFnF() 找到递归到其中的那些子文件夹时。遵循此过程可能会变得很困难，因此，如果您是新手，您可能很想聘请一些人来帮助您。

function getFileUrlsIntoB3() {
  const folder=DriveApp.getFoldersByName("RACINE").next();//assumes only one folder with that name
  getFnF(folder);  
}

以下函数将后续文件的 Id 获取到递归函数中，以便可以将数据写入文件“FOLLOW UP”的最左侧表中。它使用缓存服务，因此第一次之后的所有调用都发生得更快，因为文件 id 是直接从缓存中获取的。缓存将保持此值最多 3 分钟，但您可以根据需要调整更多。

function getFollowUpId() {
  const cs=CacheService.getScriptCache();
  const cached=cs.get('FOLLOW UP ID');
  if(cached) {
    return cached;
  }else{
    let folder=DriveApp.getFoldersByName("RACINE").next();
    let file=folder.getFilesByName("FOLLOW UP").next();
    cs.put('FOLLOW UP ID',file.getId(),180);//3 minute cache time
    return file.getId();
  }
}

这就是递归函数。这只是意味着它是一个调用自身的函数。

function getFnF(folder) {
  const ss=SpreadsheetApp.openById(getFollowUpId());
  var sh=ss.getSheets()[0];
  var files=folder.getFiles();
  while(files.hasNext()) {
    var file=files.next();
    if(file.getName().match(/^Entry_Form_ID\d{4}/)) {
      sh.getRange(getColumnHeight(2,sh,ss)+1,2).setValue(file.getUrl());
    }
  }
  var subfolders=folder.getFolders() 
  while(subfolders.hasNext()) {
    var subfolder=subfolders.next();
    if(subfolder.getName().match(/^ID\d{4}/)) {
      getFnF(subfolder);
    }
  }
}

最后，这是计算电子表格“FOLLOW UP”的 Sheet[0] 中 columnB 的当前高度的函数

function getColumnHeight(col,sh,ss){
  var ss=ss||SpreadsheetApp.getActive();
  var sh=sh||ss.getActiveSheet();
  var col=col||sh.getActiveCell().getColumn();
  var v=sh.getRange(1,col,sh.getLastRow(),1).getValues().map(function(r){return r[0];});
  var s=0;
  var h=0;
  v.forEach(function(e,i){if(e==''){s++;}else{s=0;}h++;});
  return (h-s);
}

测试

我使用以下两个函数创建了一些文件夹和文件，发现测试代码轻而易举。它只有 3 个打字错误，然后运行得很好。它只是创建 ascii 文本文件而不是电子表格，但它们仍然是文件。

function generateFoldersAndFiles() {
  const folder=DriveApp.getFolderById('RACINE FolderId');
  for(let i=0;i<4;i++) {
    let subfldr=folder.createFolder(getStrings("ID",4));
    for(let j=0;j<4;j++) {
      subfldr.createFile(getStrings("Entry_Form_ID",4),"Text Content");
    }
  }
}

function getStrings(prefix,length) {
  const nA=[0,1,2,3,4,5,6,7,8,9];
  var s=prefix;
  for(let i=0;i<length;i++) {
    s+=nA[Math.floor(Math.random()*nA.length)];
  }
  return s;
}

文件结构：