Getting ID's from google drive folder

[Jasper_VIVISOL]

Hi all,

I’m looking for a solution for the following; How to get the ID’s from files in a Google Drive Folder
Using the google drive connector - list files/folder I get the path. But for a report i need to embed the URL to the specific files.
I know how to get the ID manually (right mouse button, get link) but it’s not sustainable for my use case. I read online to use a specific function, but I’m not sure how to implement this in KNIME.

Can anyone give me any insight?

Thanks!

[duristef]

Hi @Jasper_VIVISOL,
maybe this workflow can help you.KNIME_project – KNIME Hub
It calls a short Google Apps standalone script, which returns a JSON array with name, url and id of the files in the folder whose id you provide in the GET call.

function getFileList(folderId) {
  let folder=DriveApp.getFolderById(folderId);
  let files=folder.getFiles();
  let file_list=[], f, link;
  while (files.hasNext()) {
    f=files.next();
    file_list.push({"name": f.getName(), "url": f.getUrl(), "id":f.getId()})
  }
  return file_list;
}

function doGet(e) {
  if (e && e.parameter && e.parameter.id) {
    var folderId = e.parameter.id
   }
   else {
     var folderId = "<default folder id>"
  }
  // console.log(JSON.stringify(getFileList(folderId)))
  return(ContentService.createTextOutput(JSON.stringify(getFileList(folderId)))).setMimeType(ContentService.MimeType.JSON)
}

You just have to write into the Table Creator the url of the script and the id of the folder you want to read (current values must be replaced)
The final result is a table like this one

When you deply your web app remember to “allow anyone to access”

Hope the workflow will help you

[Jasper_VIVISOL]

Hi Duristef,

Thank you, while trying to implement your solution I ran into the strict user restrictions of our corporate google suite. Do you have any insight how to bypass these and/or an alternative solution?

Jasper

[duristef]

the only alternative solution I figured out is a Python script. But it requires the creation of an OAuth 2.0 Client ID via Google Cloud Platform. If you can do that, you must download the credentials file (“something.json”) and save its path in the Table Creator node. The first time you run the workflow you will be asked to grant the app a permission to read your data. Then the script saves the token it receives into a local file so it can use it in the next runs.
In this file you find both solutions
KNIME_project.knwf (28.8 KB)

[Jasper_VIVISOL]

Hi Duristeff,

Many thanks! I was able to start the script with a designated account for KNIME within our google-domain. I will mark your first response as the solution. Thanks again for you support.

[duristef]

hi @Jasper_VIVISOL , glad to read that you found the workflow useful