Inserting image into googlesheets cell using javascript api

Question

I'm trying to figure out how to insert a user-created image into a googlesheets cell. I found this 7-year-old question that suggested using the =IMAGE function. But this doesn't seem to work with a data uri. When I try the cell gives an error saying "Function IMAGE parameter 1 value is not a valid url." I'm hoping that there's some new answer that someone has come up with in the last 7 years that will work for data uris. Here is my code:

   function save() {
        var dataURI = canvas.toDataURI();
        dataURI=dataURI.toString();
        let data = "=IMAGE(\"" + dataURI + "\")";
   
        gapi.client.sheets.spreadsheets.values.update({
            spreadsheetId: sheet,
            range: range,
            valueInputOption: "USER_ENTERED",
            resource: {"values":[[data]]},
        }).then((response) => {
            window.location.href=nextPage.html;
        });
    }

I've considered saving the image to a normal url but I'd rather the image not be publicly accessible. I've also considered having my code interact with Apps Script code using google.script.run. But I'm not really sure if SpreadsheetApp.newCellImage() will work with a data uri either so I didn't go too far into that rabbit hole yet. I'm hoping there's a more direct solution, anyways.

@Yogi is it possible to use Sheet.insertImage from API rather than Apps Script? — Gertrude
– Gertrude, Commented Feb 12 at 23:12
Function IMAGE parameter 1 value is not a valid url. Perhaps this is because there is no variable = canvas . Even the javascript documentation for toDataURL declares a variable for canvas — Tedinoz
– Tedinoz, Commented Feb 12 at 23:30

Lime Husky · Accepted Answer · 2025-02-13 14:55:17Z

0

Setting an Image in a cell using SpreadsheetApp.newCellImage()

Using SpreadsheetApp.newCellImage() do work by using the following methods,.setSourceUrl().build() and here's a code snippet that could help.

function myFunction() {
  const image = "-DataURI-"
  const imageBuild = SpreadsheetApp.newCellImage().setSourceUrl(image).build()
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1).setValue(imageBuild);
}

Sample Output:

References:

Class CellImage

edited Feb 13 at 14:55

answered Feb 13 at 14:50

Lime Husky

9231 gold badge4 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting image into googlesheets cell using javascript api

1 Answer 1

Setting an Image in a cell using SpreadsheetApp.newCellImage()

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Setting an Image in a cell using SpreadsheetApp.newCellImage()

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related