如何检索可共享链接的列表? [英] How to retrieve list of shareable links?

问题描述

对于以下内容，所有文件均为 Google表格格式.

For the following, all files are in Google Sheet format.

我的问题:我的工作环境的结构如下(文件夹/子文件夹):

My problem : My work environment is structured as follows (folders / sub-folders):

RACINE:
-> ID4522
-> ID7852
-> ID5864
-> ....

RACINE :
--> ID4522
--> ID7852
--> ID5864
--> ....

跟踪文件"FOLLOW_UP"位于RACINE文件夹中.ID4522，ID7852和ID5864文件夹是RACINE文件夹的子文件夹.

The tracking file "FOLLOW_UP" is located in the RACINE folder. The ID4522, ID7852 and ID5864 folders are subfolders of the RACINE folder.

我们在ID4522子文件夹中找到以下Google工作表文件"Entry_Form_ID4522"，在ID7852子文件夹中找到以下Google工作表文件"Entry_Form_ID7852"，…

We find in the ID4522 sub-folder the following Google sheet file "Entry_Form_ID4522", in the ID7852 sub-folder the following Google sheet file "Entry_Form_ID7852",…

重要说明:子文件("IDxxxx"格式)的数量可以随时更改，而不会发出警告.

Important clarification: The number of sub-files (of the form "IDxxxx") can vary at any time without warning.

我的愿望

很可能是通过javascript中的宏，从单元格B3以及向下的跟踪文件("FOLLOW_UP")中检索每个文件"Entry_Form_IDxxxx"的共享链接列表.子文件夹列表可以随时更改(例如，当我的客户添加带有关联的"Entry_Form_IDxxxx"文件的文件夹时).

Most likely via a macro in javascript, retrieve in the tracking file ("FOLLOW_UP") from cell B3 and down, the list of shareable links for each of the files "Entry_Form_IDxxxx". List of subfolders can change at any time (for example when my client adds a folder with the associated "Entry_Form_IDxxxx" file).

先谢谢您.

关于.杰罗姆(Jérôme)

Regards. Jérôme

推荐答案

递归文件URL的文件夹

我假设您只有一个名为RACINE的文件夹，并且其中只有一个名为"FOLLOW UP"的文件，并且数据所在的工作表是最左侧的工作表.假设这是正确的，那么此函数将从B列的= HYPERLINK()单元格中提取URL，从第3行一直到工作表的末尾.

I'm assuming that you have only one folder named RACINE and there is only one file in it that is named "FOLLOW UP" and that the sheet where your data is located in is the left most sheet. Assuming as that is true then this function will extract the urls out of =HYPERLINK() cells found in column B from row 3 on down to the end of the sheet.

最初，我跳到一个结论，即B列中有链接，并且您需要URL列表，因此此功能可能是您不想要的.请让我知道，我将其删除.

Originally I jumped to the conclusion that there we're links in column B and that you wanted a list of URL's so this function is probably not want you want. Please let me know and I'll remove it.

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列表，如以下段落所述.我在此答案中使用最后两个函数生成的一些数据上对此进行了测试.修正了几次输入错误后，它在正确的位置运行了非常漂亮的网址.

I think this is what you really want and that is a list of urls to the files in your working subdirectories that meet a certain naming format as explained in the following paragraphs. I tested this on some data I generated with the last two functions in this answer. After fixing a couple of typos it ran very nice and producted url in the correct location.

第一个功能基本上是启动器.假设您在Google驱动器上只有一个名为"RACINE"的文件夹.它调用一个递归函数，该函数本质上搜寻所有RACINE的子文件夹，以查找格式为"Entry_Form_IDxxxx"的文件，其中xxxx均为0-9之间的数字.当找到类似的文件名时，它将把该URL加载到"FOLLOW UP"的sheet [0]中columnB底部的下一个空单元格中.它还搜索格式为"IDxxxx"的子目录，其中xxxx均为0到9的数字.当它通过从getFnF()内部调用getFnF()来递归找到这些子文件夹时.遵循这个过程可能会很困难，因此，如果您是新手，您可能会想雇用一些人来帮助您.

The first function is basically the starter. It assumes that you have only one folder named "RACINE" on your google drive. It calls a recursive function which essentially crawls through all of the RACINE's subfolders looking for files of the form "Entry_Form_IDxxxx" where xxxx are all numbers between 0-9. When it finds a file name like that it loads that url into the next empty cell at the bottom of columnB in sheet[0] of "FOLLOW UP". It also searches for subdirectories of the form "IDxxxx" where xxxx are all numbers from 0-9. When it finds those subfolders in recurses into them by calling getFnF() from inside of getFnF(). It can get hard to follow this process so if your new to it you may very well want to hire some to help you.

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

以下函数将后续文件的ID放入递归函数中，以便可以将数据写入文件"FOLLOW UP"的最左侧.它使用缓存服务，因此在第一次调用之后的所有调用都可以更快地进行，因为文件ID是直接从缓存获取的.缓存将最多保留此值3分钟，但您可以根据需要进行调整.

The following function gets the Id for the follow up file into the recursive function so that data can be written into the most left hand sheet of file "FOLLOW UP". It uses the cache service so that all calls after the first happen considerably faster since the file id is taken directly from cache. The cache will hold this value for upto 3 minutes but you can adjust for more if you wish.

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

这是递归函数.这只是意味着它是一个自我调用的函数.

And this is the recursive function. Which just means that it's a function that calls itself.

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]中B列的当前高度的函数

And finally this is the function that calculates the current height of columnB in Sheet[0] of spreadsheet "FOLLOW UP"

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文本文件而不是电子表格，但它们仍然是文件.

I used the following two functions to create some folders and files and found that testing the code was a breeze. It had only 3 typo type failures and then ran just fine. It's just creating ascii text files not spreadsheets but they're files none the less.

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;
}

文件结构:

这篇关于如何检索可共享链接的列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！