convert data column of type file to base64 on upload

[EsonC]

Hello,

I'm creating an app where a user can add a file from their desktop through appsheet. This appsheet has an automation configured on it which will trigger a google script based on a button in the app

This google script will send the document to another application through API. However, the API expects the document to be base64 encoded.

I tried retrieving the uploaded file by ID but it seems that this doesn't work for files uploaded through appsheet

  var uri = Utilities.base64Encode((DriveApp.getFileById(ID).getBlob().getBytes()));

 This returns this error when I test my automation:

Failed: Error: Apps Script execution failed with code "System" and message: Error deserializing ObjectId: input_Files_/eson.callpani@ausy.be.doc.113359.docx 

How could I convert documents uploaded through appsheet to base64 so that the API accepts it?

Thanks

[Koichi_Tsuji · Accepted solution]

Once you upload files from AppSheet, then you can access to the file using url of ; -

CONCATENATE(
  "https://www.appsheet.com/template/gettablefileurl",
  "?appName=", ENCODEURL(CONTEXT("AppName")),
  "&tableName=", ENCODEURL(CONTEXT("Table")),
  "&fileName=", ENCODEURL([image-or-file-column])
) 

https://help.appsheet.com/en/articles/961605-displaying-images-and-documents

Within your script, you pass this URL and convert to base64, then  pass to your endpoint as you want.

Thanks to the recent addition of GAS as task under automation, you could trigger GAS function on any event that fits with your use case, such as onload new files etc.

It is possible GAS function runs before the actual file is save to Google Drive, so it could be worth adding "Wait for" step inside your automation config.

[Steve]

AppSheet does not support base64-encoding.

[EsonC]

Is there no way to convert the uploaded file which is inputted by appsheet on cell E2 to base64 by using google script? What format do uploaded files come in when initially uploaded through appsheet?

[Koichi_Tsuji · Accepted solution]

Once you upload files from AppSheet, then you can access to the file using url of ; -

CONCATENATE(
  "https://www.appsheet.com/template/gettablefileurl",
  "?appName=", ENCODEURL(CONTEXT("AppName")),
  "&tableName=", ENCODEURL(CONTEXT("Table")),
  "&fileName=", ENCODEURL([image-or-file-column])
) 

https://help.appsheet.com/en/articles/961605-displaying-images-and-documents

Within your script, you pass this URL and convert to base64, then  pass to your endpoint as you want.

Thanks to the recent addition of GAS as task under automation, you could trigger GAS function on any event that fits with your use case, such as onload new files etc.

It is possible GAS function runs before the actual file is save to Google Drive, so it could be worth adding "Wait for" step inside your automation config.

[EsonC]

Hey Koichi,

This seems like a good way forward. I created a virtual column with the formula as described and pass the value of this column to my script

This is my uploaded document input:

input_Files_/eson.callpani@ausy.be.Attach document.133656.docx

This is the virtual column input:

https://www.appsheet.com/template/gettablefileurl?appName=CopyofTelewerkPolicy_CXO-6324719&tableName=input&fileName=input_Files_%2Feson.callpani%40ausy.be.Attach%20document.133656.docx

In my script I convert it by doing:

var uri = Utilities.base64Encode((DriveApp.getFileById(https://www.appsheet.com/template/gettablefileurl?appName=CopyofTelewerkPolicy_CXO-6324719&tableName=input&fileName=input_Files_%2Feson.callpani%40ausy.be.Attach%20document.133656.docx).getBlob().getBytes()));

 When running the script I receive the same error:

Could you point out what I'm doing wrong?

[Koichi_Tsuji]

Try to quote the url, and see how it goes.

var uri = Utilities.base64Encode((DriveApp.getFileById('https://www.appsheet.com/template/gettablefileurl?appName=CopyofTelewerkPolicy_CXO-6324719&tableName=input&fileName=input_Files_%2Feson.callpani%40ausy.be.Attach%20document.133656.docx').getBlob().getBytes()));

[EsonC]

I tried quoting it and afterwards inputting the variable in the main function but stilll same  error persists.

function myFunction(ID,email,name) {
  var uri = Utilities.base64Encode((DriveApp.getFileById('https://www.appsheet.com/template/gettablefileurl?appName=CopyofTelewerkPolicy_CXO-6324719&tableName=input&fileName=input_Files_%2Feson.callpani%40ausy.be.Attach%20document.133656.docx').getBlob().getBytes()));
  var url = "exampleurl.com";

  var payload = {
  "documents": [
    {
      "documentBase64":uri,
      "documentId": 1234,
      "fileExtension": "docx",
      "name": "example doc 2022"
    }

When  trying to run the appscript seperatley from the appsheet by using the file url generated  in the appsheet i receive this error:

Error
Exception: Unexpected error while getting the method or property getFileById on object DriveApp.

When trying  to run the script from within the appsheet by  using the automation testing environment, still the same error persists:

Seems like the Utilities.Base64Encode function is  failing to retrieve the file by  id for urls generated through appsheet file upload.

Any other possible solutions I could explore?

[Koichi_Tsuji · Accepted solution]

Your code is simply wrong to convert URL to blob. UrlFetchApp class should be used with fetch method.

Sample code seems like this 

function base64(){

var response = UrlFetchApp.fetch('https://www.appsheet.com/template/gettablefileurl?appName=CopyofTelewerkPolicy_CXO-6324719&tableName=input&fileName=input_Files_%2Feson.callpani%40ausy.be.Attach%20document.133656.docx');

var blob = response.getBlob();

var bytes = blob.getBytes();

var base64String = Utilities.base64Encode(bytes);

Logger.log(base64String);

}

[EsonC]

Hmm that's odd, i have a working example  of where i do all these things in one step via

 var uri = Utilities.base64Encode((DriveApp.getFileById('https://www.appsheet.com/template/gettablefileurl?appName=CopyofTelewerkPolicy_CXO-6324719&tableName=input&fileName=input_Files_%2Feson.callpani%40ausy.be.Attach%20document.133656.docx').getBlob().getBytes()));

 However,  your suggestion works. Thanks a lot, now i know how to use file/images within appsheet to scripts 😊