Add image into Gsheet

I want to upload an image taken in a specific GSheet cell.
I couldn't find the correct method. Can you help me?

Google apps script required:

Upload image to Google Drive
Return the file ID of the uploaded image
Insert formula to sheet cell containing the path to the image

This is "close" to what needs to be done

https://groups.google.com/g/mitappinventortest/c/rpWItwBBYS0/m/YSlBAv6GCAAJ

cell formula required, something like this:

=image("https://drive.google.com/uc?id=1M0BQT8wzizUwsKHxp_JkkDpDa5RMaJLm")
2 Likes

I need help to get it all done.
Let's proceed with the various steps:

Upload image to Google Drive
Return the file ID of the uploaded image

How?

See

1 Like

With the aia file in your link I was able to upload the selected file.
Now I have to build the same thing with my app.
These are the blocks and they don't work.
The file that I have to upload is an image derived from a canvas.
How can I do?

I see two issues:

  1. You do not have a block to encode the image file to base64 ?
  2. You appear to be setting the mimetype to a directory (ASD) ?
1 Like
  1. I have the file png into folder app and i want upload this file. Can I encode? Have you a example blocks?

Please share your aia project

1 Like

This is aia project.
Rapporto_Gsheet.aia (73.1 KB)

In order to do everything I need to be able to insert the drive image inside a cell.
The image is the client's signature on an employment report.

Try this

Rapporto_Gsheet_revised.aia (73.1 KB)

1 Like

Thx work fine!!!
Now, the second step is insert the image uploded into Gsheet cell.
How?

Something like this (I uploaded an image file to the assets, then copied this over to the ASD, which is where your canvas image will be saved):

SCRIPT
(the script is bound to the spreadsheet)

function doPost(e) {

    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheetByName('Sheet1');
    var data = Utilities.base64Decode(e.parameters.data);
    var blob = Utilities.newBlob(data, e.parameters.mimetype, e.parameters.filename);
    var fileID = DriveApp.getFolderById(e.parameters.folderId).createFile(blob).getId();
    sh.appendRow(['=image("https://drive.google.com/uc?id=' + fileID + '")',fileID,e.parameters.filename[0]]);
    return ContentService.createTextOutput("Image Uploaded: " + fileID);
    }

BLOCKS

SCREEN
image

SHEET

Not wok... :frowning:

function doPost(e) {

    var ss = SpreadsheetApp.openByUrl ("https://docs.google.com/spreadsheets/d/14IyXitAderubyu4Zclqj2lPipPlFN1PmlRO6Mw54DFg/edit#gid=0");
    var sh = ss.getSheetByName('Rapportino');
    var data = Utilities.base64Decode(e.parameters.data);
    var blob = Utilities.newBlob(data, e.parameters.mimetype, e.parameters.filename);
    var fileID = DriveApp.getFolderById(e.parameters.folderId).createFile(blob).getId();
    sh.appendRow(['=image("https://drive.google.com/uc?id=' + fileID + '")',fileID,e.parameters.filename[0]]);
    return ContentService.createTextOutput("Image Uploaded: " + fileID);
    }

This doesn't help....

What doesn't work, what response do you get in Label3 ?

Only the image ID without "uploaded image" and in the sheet I don't have the uploaded image.

I had a quick look at your spreadsheet....

appendRow() will add a row below the last row of your sheet - Rapportino

where were you expecting it to go ?

I would like the image to go to cell R44.

You will need to edit your script:

replace:

sh.appendRow(['=image("https://drive.google.com/uc?id=' + fileID + '")',fileID,e.parameters.filename[0]]);

with (should be...)

sh.getRange("R44").setFormula('=image("https://drive.google.com/uc?id=' + fileID + '")');

With the change in the cell I have the formula: "=image("https://drive.google.com/uc?id=1_jU2wiVP4Fo89zsSg_aaLjsyFH_CSAep")" but the image is not seen.

Did you use single quotes ' where I used single quotes ?

It is working OK for me here.