LinkedIn
Twitter
(*NOTE: Only applicable for Google users (GSheet/GDrive))
As you all know, when a file column is used in your apps and your users upload a file, Appsheet does not retain the same filename as the user uploaded it.
The default filename saved to Google Drive is
[KEY_COLUMN].[COLUMN_NAME].[UTC-TIMESTAMP].[FILE EXTENSION]
No issues as the files can easily be accessible via the app. But what if the backend users would want to identify the files easily when browsing through them via Google Drive interface?
This was not possible before, but luckily, Appsheet announced the new feature Use return values from Apps Script tasks which allows us to take advantage of this power script.
In a nutshell, here are the things you will need.
1. Setup the Image/Folder path of your [FILE] column.
In the example above, all files will be saved in a root folder named URC Bank Docs, under a subfolder named after the [MERCHANT] column ("/URC Bank Docs/COMPANY ABC/the file here.ext")
2. Decide which columns in your table will be used as components of the file name you want to set to.
For example, in an app tracking received checks from merchants, the following columns are available:
- CHEQUE_NO :: Text (Key)
- MERCHANT :: Text
- AMOUNT :: Decimal
And let's say you want your file names to be saved as
[MERCHANT] & "-Check No " & [CHEQUE NO] & "-" & [AMOUNT]
(ie. COMPANY ABC-Check No 123456-1000)
3. Get the Google Drive folder id of the folder where files will be saved
You can get this from the folder url in Google Drive
(ie https://drive.google.com/drive/folders/1JAvB0fDTpayABCDEFG7vGBbNVOC1U794)
4. Create a new App Script file in Google Drive
Rename the file however you want.
Since we want the filename to be based on three (3) columns of our table, we will have to pass those as parameters to our function ( function name(merchant, checkNo, amount) )
For the example in this post, here is the full script:
function renameFile(merchant, checkNo, amount) {
//Log Request
console.log(`Received request for ${merchant} with Cheque No ${checkNo} and amount of ${amount}`);
let folder = DriveApp.getFolderById("Your folder id from Step 3");
let subfolder = folder.getFoldersByName(merchant);
if(subfolder.hasNext()) {
let subfolderObj = subfolder.next();
let subfolderId = subfolderObj.getId();
// Get the folder id of the subfolder created via the [FILE] column's Image/File Path
let merchantFolder = DriveApp.getFolderById(subfolderId);
// Get all the files inside the folder
let files = merchantFolder.getFiles();
let file;
// Loop through each file in the folder
while (files.hasNext()) {
file = files.next();
filename = file.getName();
if (filename.includes( checkNo )) {
// get the file extension of the uplaoded file
let ext = filename.split(".").pop();
console.log( filename );
// Rename the file
let newName = `${merchant}-Check No ${checkNo}-Php${amount}.${ext}`;
//Rename the file in Google Drive
file.setName(newName);
let newValue = `/UCR Bank Docs/${merchant}/${newName}`;
return newValue; // Return the new value to AppSheet
}
} // while files.hasNext()
} // if subfolder.hasNext()
} // end function renameFile
Save the file and run the function at least once to allow appropriate permissions.
5. Create a Bot that Monitors Adds and Deletes to [FILE] column of the table, then renames the newly uploaded file in GDrive
Event
Event Type: Data Change - Adds and Updates
Table: Your AppSheet table
Condition:
AND(
ISNOTBLANK([FILE]),
[_THISROW_BEFORE].[FILE]<>[_THISROW_AFTER].[FILE]
)
Process
Step Name: RENAME FILE :: Run a task - Call a script
Table name: Your AppSheet table
Apps script Project: Search for the project you created in Step 4
Function Name: the function name in the Apps Script file (renameFile(merchant, checkNo, amount))
Function Parameters:
This is where we will pass values from our columns to the Appscript function.
Return value: Turned on
Specific Type: Text
6. Add another step to also update the column values in your [FILE] column to reflect the new filename
Task Name: Update File Path :: Set row values
Set these columns:
[FILE] = [RENAME FILE].[Output]
[RENAME FILE] is the step name you created calling the Appscript function. You may change this accordingly. .[Output] allows us to retrieve the returned values of the script and use it in our task step.
7. TEST and watch the magic happen.
