How to retrieve list of shareable links?

Question

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
--> ....

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

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",…

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

My wish

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).

Thank you by advance.

Regards. Jérôme

Answer 1

Yes there is only one folder named RACINE and one file named "FOLLOW_UP". I do not understand by the way that there can be several folders and several files with the same name? I remind you that I am not a developer.

The names of subfolders (of type IDxxxx) are also unique. I specify that the name of the sub-folders can also take the form IDxxxx_x (for example ID4522_1).

The IDxxxx and IDxxxx_x subfolders are all located in the RACINE folder.

There is an Entry_Form_IDxxxx (or Entry_Form_IDxxxx_x) file in each IDxxxx (and IDxxxx_x) sub-folder. There is only one Entry_Form_IDxxxx and Entry_Form_IDxxxx_x file.

For example in the ID4522 sub-folder there is the file "Entry_Form_ID4522" (there is only one file "Entry_Form_ID4522"). For example in the ID4522_1 sub-folder there is the file "Entry_Form_ID4522_1" (there is only one file "Entry_Form_ID4522_1").

In each IDxxxx or IDxxxx_x folder there is only one Entry_Form_... file.

I would like to put screenshots to better explain my problem but I do not know how to do it?

The file in which I would like to retrieve the results (that is, the shareable links of the "Entry_Form_IDxxxx" files) is called "FOLLOW_UP" ("feuille 1" tab).

In the "FOLLOW_UP" file ("feuile 1" tab), here is what I would like to obtain:

For example in cell B3: https://docs.google.com/spreadsheets/d/1VUf_t1gkv2lddfgsfgds7UhJeunrNzo2-QGcRe24/edit?usp=sharing

This link would correspond to the link of the first file found, for example "Entry_Form_ID4522"

In cell B4: https://docs.google.com/spreadsheets/d/1VUf_jkghkdxjfghgjgjzo2-QGcRe24/edit?usp=sharing

This link would correspond to the link of the second file found, for example "Entry_Form_ID7852"

Answer 2

Recurse Folders for File URLs

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.

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
}

After jumping to conclusions

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.

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

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

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

TESTING

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

File Structure: