CODEWITHSUNDEEP
javascriptimagegoogle-sheetsgoogle-apidata-uri
Gertrude
Gertrude

Reputation: 13

Inserting image into googlesheets cell using javascript api

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.

Upvotes: 1

Views: 68

Answers (1)

Lime Husky
Lime Husky

Reputation: 890

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:

Image Output

References:

Upvotes: 1

Related Questions