DocumentationFormulasFunctionsImage

IMAGE function

PRO

The IMAGE function in Jspreadsheet Formulas Pro places an image inside a cell using its address. Instead of uploading pictures or configuring a dedicated column type, you reference the URL stored in another cell and the grid renders the picture, which is convenient for product catalogs, team directories or dashboards with logos. Only secure sources are accepted: https URLs and inline data URIs.

Documentation

Returns an image from an https URL or data URI that is rendered inside the cell.

Category

Lookup and reference

Syntax

IMAGE(source, [alt_text], [sizing], [height], [width])

Parameter Description
source The address of the image. It must be an https URL or a data URI such as data:image/png;base64,....
[alt_text] Optional. The alternative text that describes the image.
[sizing] Optional. The sizing mode, 0 to 3, following the Excel convention: 0 = fit the cell keeping the aspect ratio (default), 1 = fill the cell ignoring the aspect ratio, 2 = original size, 3 = custom size using height and width.
[height] Optional. The height in pixels. Only accepted together with sizing 3.
[width] Optional. The width in pixels. Only accepted together with sizing 3.

Behavior

The IMAGE function returns an image entity that holds the source and the alternative text. The grid recognizes this entity and renders the picture inside the cell instead of the URL.

  • Only https addresses and data URIs are accepted as source. An http address returns #BLOCKED! and any other value, such as a relative path, an ftp address, a number or a logical value, returns #VALUE!.
  • An empty source returns an error, so wrap the formula with IF or IFERROR when the URL column can be blank.
  • alt_text is stored with the image and can be any text, including an empty string.
  • sizing accepts 0, 1, 2 or 3. height and width are validated as numbers and are only accepted when sizing is 3.
  • The formula does not download or verify the file. A valid https address pointing to a missing file returns the entity normally and the cell shows a broken image.

Common Errors

Error Description
#BLOCKED! Returned when source uses the http protocol. Only https and data URIs are allowed.
#VALUE! Returned when source is not a valid https URL or data URI, when sizing is outside 0 to 3, when height or width is not numeric, or when height or width is provided with a sizing other than 3.
#ERROR! Returned when source is an empty cell.

Best practices

  • Store the URLs in their own column and reference them, so the addresses stay editable and the image column only holds formulas.
  • Use https addresses served with permissive CORS headers, or data URIs for small icons, to make sure the browser can display the picture.
  • Always provide alt_text for accessibility and for exports where the picture cannot be rendered.
  • Increase the row height of the worksheet so the rendered image is legible.
  • Guard optional URL cells with IF(B2="", "", IMAGE(B2)) to avoid errors in empty rows.

Usage

A few examples using the IMAGE function.

IMAGE("https://jspreadsheet.com/templates/default/img/logo.png")
// Renders the image inside the cell

IMAGE("https://jspreadsheet.com/templates/default/img/logo.png", "Jspreadsheet logo")
// Same image with alternative text

IMAGE("https://example.com/chart.png", "Chart", 3, 40, 60)
// Custom size mode with height and width

IMAGE("data:image/png;base64,iVBORw0KGgo...", "Inline icon")
// Inline image encoded as a data URI

IMAGE("http://example.com/photo.png")
// Returns #BLOCKED! because http is not allowed

IMAGE("photo.png")
// Returns #VALUE! because relative paths are not accepted

Interactive Spreadsheet Demo

<html>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/index.min.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet@13/dist/jspreadsheet.min.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('OWMzYjdhZjQzZmEzMGYwYmE1Mzk5MjlhNmEwZWJlZjBjODk4YzY5ZmFkYmUyZjExMThiMjlhOGE4ODBlODlmN2FiMzhmN2U2MThmODhhYmFkMmMwYmIxZDJkZWNlZGFlZjk5ZDdlYzc3OWY1YzYyMTI2NGM1ODUzNmUwOTAzMzcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNalE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Framework",
        "Image URL",
        "Preview"
    ],
    [
        "JavaScript",
        "https://jspreadsheet.com/templates/default/img/javascript.png",
        "=IMAGE(B2, A2)"
    ],
    [
        "Angular",
        "https://jspreadsheet.com/templates/default/img/angular.png",
        "=IMAGE(B3, A3)"
    ],
    [
        "Jspreadsheet",
        "https://jspreadsheet.com/templates/default/img/logo.png",
        "=IMAGE(B4, A4)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('OWMzYjdhZjQzZmEzMGYwYmE1Mzk5MjlhNmEwZWJlZjBjODk4YzY5ZmFkYmUyZjExMThiMjlhOGE4ODBlODlmN2FiMzhmN2U2MThmODhhYmFkMmMwYmIxZDJkZWNlZGFlZjk5ZDdlYzc3OWY1YzYyMTI2NGM1ODUzNmUwOTAzMzcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNalE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Framework",
        "Image URL",
        "Preview"
    ],
    [
        "JavaScript",
        "https://jspreadsheet.com/templates/default/img/javascript.png",
        "=IMAGE(B2, A2)"
    ],
    [
        "Angular",
        "https://jspreadsheet.com/templates/default/img/angular.png",
        "=IMAGE(B3, A3)"
    ],
    [
        "Jspreadsheet",
        "https://jspreadsheet.com/templates/default/img/logo.png",
        "=IMAGE(B4, A4)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('OWMzYjdhZjQzZmEzMGYwYmE1Mzk5MjlhNmEwZWJlZjBjODk4YzY5ZmFkYmUyZjExMThiMjlhOGE4ODBlODlmN2FiMzhmN2U2MThmODhhYmFkMmMwYmIxZDJkZWNlZGFlZjk5ZDdlYzc3OWY1YzYyMTI2NGM1ODUzNmUwOTAzMzcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNalE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Framework",
        "Image URL",
        "Preview"
    ],
    [
        "JavaScript",
        "https://jspreadsheet.com/templates/default/img/javascript.png",
        "=IMAGE(B2, A2)"
    ],
    [
        "Angular",
        "https://jspreadsheet.com/templates/default/img/angular.png",
        "=IMAGE(B3, A3)"
    ],
    [
        "Jspreadsheet",
        "https://jspreadsheet.com/templates/default/img/logo.png",
        "=IMAGE(B4, A4)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import formula from "@jspreadsheet/formula-pro";

// You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox.
// The license is valid for one day, after which the spreadsheet will become read-only.
// For a longer trial period, you can create a free account and generate a demo license with an extended expiration date.
jspreadsheet.setLicense('OWMzYjdhZjQzZmEzMGYwYmE1Mzk5MjlhNmEwZWJlZjBjODk4YzY5ZmFkYmUyZjExMThiMjlhOGE4ODBlODlmN2FiMzhmN2U2MThmODhhYmFkMmMwYmIxZDJkZWNlZGFlZjk5ZDdlYzc3OWY1YzYyMTI2NGM1ODUzNmUwOTAzMzcsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpreE1ESTRNalE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW5ZeE15SXNJbU5vWVhKMGN5SXNJbVp2Y20xeklpd2labTl5YlhWc1lTSXNJbkJoY25ObGNpSXNJbkpsYm1SbGNpSXNJbU52YlcxbGJuUnpJaXdpYVcxd2IzSjBaWElpTENKaVlYSWlMQ0oyWVd4cFpHRjBhVzl1Y3lJc0luTmxZWEpqYUNJc0luQnlhVzUwSWl3aWMyaGxaWFJ6SWl3aVkyeHBaVzUwSWl3aWMyVnlkbVZ5SWl3aWMyaGhjR1Z6SWl3aVptOXliV0YwSWl3aWNHbDJiM1FpWFN3aVpHVnRieUk2ZEhKMVpYMD0=');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Framework",
        "Image URL",
        "Preview"
    ],
    [
        "JavaScript",
        "https://jspreadsheet.com/templates/default/img/javascript.png",
        "=IMAGE(B2, A2)"
    ],
    [
        "Angular",
        "https://jspreadsheet.com/templates/default/img/angular.png",
        "=IMAGE(B3, A3)"
    ],
    [
        "Jspreadsheet",
        "https://jspreadsheet.com/templates/default/img/logo.png",
        "=IMAGE(B4, A4)"
    ]
]
            }]
        });
    }
}