Recipes

Charts in Google Sheets

Show a chart in a cell with =IMAGE, built from the rows of your sheet.

The result

The sheet

The chart reads this data. Labels in A, numbers in B, the series name in B1.

Copy these formulas

D1: builds the chart config as text

=CONCATENATE("{""type"":""bar"",""data"":{""labels"":[""",TEXTJOIN(""",""",TRUE,A2:A6),"""],""datasets"":[{""label"":""",B1,""",""data"":[",TEXTJOIN(",",TRUE,B2:B6),"]}]}}")

E1: turns the config into an image

=IMAGE("https://chartpng.dev/chart?w=600&h=360&c="&ENCODEURL(D1))

ENCODEURL makes the config safe for a query string. TEXTJOIN lists the labels and the values. Add rows by widening the ranges, for example A2:A20 and B2:B20.

One cell instead of two

=IMAGE("https://chartpng.dev/chart?w=600&h=360&c="&ENCODEURL(CONCATENATE("{""type"":""bar"",""data"":{""labels"":[""",TEXTJOIN(""",""",TRUE,A2:A6),"""],""datasets"":[{""label"":""",B1,""",""data"":[",TEXTJOIN(",",TRUE,B2:B6),"]}]}}")))

Set the size inside the cell

Mode 4 takes a height, then a width, in pixels. The ratio of this chart is 600 by 360, so 400 by 240 keeps it.

=IMAGE("https://chartpng.dev/chart?w=600&h=360&c="&ENCODEURL(D1), 4, 240, 400)

Limits that matter

• Locale. The formulas on this page use commas between arguments and a dot as the decimal mark. In a sheet whose locale uses semicolons between arguments, Sheets may reject them: replace each comma that separates arguments with a semicolon (the commas inside the quoted text stay). A decimal comma, as in 12,5, is joined as 12,5 and splits the data array in two. Use whole numbers, or set the locale to one with a dot under File, Settings, Locale. Not tested here: we did not run these formulas in a sheet with a comma-decimal locale.
• PNG, not SVG. IMAGE does not accept SVG. ChartPNG always answers with a PNG, so it works here.
• The cell does not grow. Make the row taller and the column wider yourself. No mode resizes the cell to the image.
• Quotes break the JSON. A label with a double quote or a backslash in it makes the config invalid JSON, and ChartPNG answers with an error instead of an image. Clean the labels first.
• Config size. The config can be up to 16 KB. Past that, ChartPNG refuses the request.
• Free plan. A formula builds a plain URL, so the image carries the small chartpng.dev watermark and the free rate limits.
• Only the chart types ChartPNG renders: bar, line, pie, doughnut, radar, polarArea, scatter and bubble.
• Check the cell. A bad config returns a JSON error instead of an image. Open the URL from the formula in a browser to read the message.