IF you just need a script to retrieve a file that has already been created in a Folder in your drive and return the link. This script will take the file name passed from appsheets and the File ID (This can be made static in the script if needed). The function uses sleep and waits 60 seconds. Then goes to the file and subfolders inside it and either matches the file name or based on a partial match as well if you use keys for the name. Then takes the file ID and creates the File URL and returns as a text string to appsheets to be pasted in your row cell of choice. Also lots of loggers to diagnose if needed. I recommend removing the ones that state the file id and folder id when you move to full roll out ๐
function getFileId(fileName, folderId) {
Logger.log("Starting getFileId function...");
// Pause for 60 seconds (60000 milliseconds)
Logger.log("Pausing execution for 60 seconds...");
Utilities.sleep(60000);
Logger.log("File name (exact or partial match): " + fileName);
Logger.log("Folder ID: " + folderId);
try {
// Get the folder by ID
var folder = DriveApp.getFolderById(folderId);
if (!folder) {
throw new Error("Folder not found with ID: " + folderId);
}
Logger.log("Folder found: " + folder.getName());
// Check for the file in the main folder
Logger.log("Checking in the main folder...");
var fileLink = searchFolderForFile(fileName, folder);
if (fileLink) {
Logger.log("File found in main folder. Returning link: " + fileLink);
return ContentService.createTextOutput(fileLink).setMimeType(ContentService.MimeType.TEXT);
}
// If not found, recursively search subfolders
Logger.log("File not found in the main folder. Checking subfolders...");
var subfolders = folder.getFolders();
while (subfolders.hasNext()) {
var subfolder = subfolders.next();
Logger.log("Checking subfolder: " + subfolder.getName());
fileLink = searchFolderForFile(fileName, subfolder);
if (fileLink) {
Logger.log("File found in subfolder. Returning link: " + fileLink);
return ContentService.createTextOutput(fileLink).setMimeType(ContentService.MimeType.TEXT);
}
}
throw new Error("File not found with a matching name: " + fileName);
} catch (error) {
Logger.log("Error occurred: " + error.toString());
// Return the error as plain text
return ContentService.createTextOutput("Error: " + error.toString()).setMimeType(ContentService.MimeType.TEXT);
}
}
// Helper function to search for the file in a given folder
function searchFolderForFile(fileName, folder) {
var files = folder.getFiles();
while (files.hasNext()) {
var file = files.next();
if (file.getName().includes(fileName)) { // Partial or exact match
var fileId = file.getId();
Logger.log("File match found. Viewable link: " + viewableLink);
return viewableLink;
}
}
return null; // File not found in this folder
}
LinkedIn
Twitter
