METRIC RAT AI2 - Upload & Download Files to & from Google Sheets as Base64

Upload & Download Files

to & from Google Sheets as Base64 Strings

INTRO

It may be that you want to store files in a spreadsheet as base64 strings, as opposed to storing them as binary files in online storage.

This is one way to do this.

I use a google apps script to assist with the upload and setting of cells with the base64 string, the string is split at 30,000 characters, in order to help keep any resultant generated csv compliant. I use gviz query to select (by file name) the data for return to the app, and the app glues it all back together.

I use images in my example/demo, because this provides a straightforward visual output, but the method should be possible with any file type.

I use my ImageToBase64 extension for the base64 conversions in the app

WORKFLOW

First we setup a spreadsheet to receive the base64 strings, and get the File ID.
We then create a bound apps script project, and build the web app to handle the upload. Deploy the web app and get the Script Url.
Then start a new aia project and populate with two image components, two buttons, two textboxes, and a label

We start by displaying an image in an image component:

Then fill in the textboxes with the filename and the mimetype. These are useful for either exporting the spreadsheet to a csv, converting the base64 to a binary file using a script, or for displaying a datauri in html.

Then click the button to Upload the File.

This generates the base64 string and calls the afterBase64 event. In here, we call the web app, and send the base64, the filename and mimetype to it.

The web app receives the post text, adds the filename and mimetype to an array, and then proceeds to split the base64 string into chunks of 30,000 characters which are also added to the array. (Max cell size in Google Sheets is currently 50,000 characters, so you could go higher depending on your requirements).

Web App Script

//https://stackoverflow.com/a/71168026/2030549

function doPost(e) {

const ss = SpreadsheetApp.openById("1UV2qxG7cOp3EBDbC7qeOWTFLm2xyw2N9SorLfrToq7I");

const sh = ss.getSheetByName("Sheet1");

const s = e.parameter.data;

const v = [...s];

const res = [];

res.push(e.parameter.filename);

res.push(e.parameter.mimetype);

while (v.length > 0) {

res.push(v.splice(0, 30000).join(""));

}

sh.appendRow(res);

return ContentService.createTextOutput("File: " + e.parameter.filename + " saved to spreadsheet as base64");

}

The array is then appended to the next empty row in the spreadsheet, and a message is returned as responseContent to the app.

This image file actually generates 19 columns in its row...@ 500,000 characters in the base64 string. The original jpg file is 269kB. (Google Sheets can handle up to 18278 columns, so theoretically you can do this with files up to @280mb in size).

In order to return the base64 to the app, we use a gviz query on the filename (therefore, for this example, filenames need to be unique)

When the data is returned to the app in the responseContent, we first have to use the split text at , to generate an AI2 list. (the list from csv row/table blocks cannot cope with the size of the csv fields). Then we can extract and remove the filename and mimetype for reuse, before combining the remaining list elements to regenerate the base64 string. In this example, we convert the base64 string back to an image and display it in an image component.

You can return the base64 string from the Image2.Picture block.

SUMMARY

There you have it, convert an image to a base64 string, upload this and store it in a spreadsheet, retrieve the base64 string from the spreadsheet and convert it back into an image.

UPDATE

According to this:

https://stackoverflow.com/a/75720244/2030549

If using appendRow, it is possible to breach the 50,000 character barrier for a cell. I will test this out!

Page updated

Google Sites

Report abuse