Borrowing the complete code (Google App Script) from the web, like stackoverflow, such as
The function below is going to list up the file names and file Id as well as file size for the target folder.
function list_all_files_inside_one_folder_without_subfolders(){
var sh = SpreadsheetApp.getActiveSheet();
var folder = DriveApp.getFolderById(โChangeHereToYourFileFolderIdโ);
var list = ;
list.push([โNameโ,โIDโ,โSizeโ]);
var files = folder.getFiles();
while (files.hasNext()){
file = files.next();
var row =
row.push(file.getName(),file.getId(),file.getSize())
list.push(row);
}
sh.getRange(1,1,list.length,list[0].length).setValues(list);
}
We set the target folder ID onto the Appsheet App file folder Id. Then we are able to list up the file name + file IDs, great. Can instantlly pull the names and file ID of ALL the files save through a particular app.
I keep the file name as key for the table, and read the sheet as data source to my app.
On the appsheet table, we are able to manipulate the file, image type data fields using virtual column in order to get the file name alone. I set this virtual column to look up the table containing the list of file name and file id through the ref connect.
By doing this, we are able to get the google drives file Id, almost automatically. Having file Id for each saved file, we are able to construct the URL to download the target file. Awesome.
The problem is how to rerun the code when the new file is uploaded to the folder to get the most latest list of file name and ID. On the GAS setting we are able to set TRIGGER, without coding. Set up the trigger to run the function โon changeโ event over the sheet.
Furthermore, make sure to make the folder URL made available to anyone who has folder URL.
Moving on.
On the appsheet, create action to add dummy new row to the table for the file and file ID. This action is set to be fired once the form view is saved, meaning the new file is saved through the appsheet form. Triggering the action will โappendโ a dummy row at the bottom of the sheet, but it is just intended to trigger Google Apps Script to get the latest list of file names and files ID.
Yes, the bit of coding is involved in this process, but actually we just copy and paste the code with your own folder ID, otherwise we stay with โNo Codeโ, that s the beauty of Appsheet.
Whenever we upload the file through the appsheet, we capture the Google Drive file ID. Then we can manipulate that, such as contruct the publich URL to view the image, construct the download URL etc, probably more.
Downside of this tips and tricks is we need to make the folder published, meaning less secured. The google app script will run to retrieve whole list of the files inside the folder, rather than append the newly added file information.
File ID is not immidiately available after adding new file as it requires full sync, as things are happning on the back-end.
LinkedIn
Twitter
