Google-sheets – Export images from formulas in Google Sheets

google sheets

I have a Google Sheet that has a column generating images (barcodes in this case) based off of data from other cells via the =image(url) function. I thought this would be a great way to quickly create the hundreds of barcodes I need instead of manually creating one at a time.

The barcodes generate perfectly (using barcodesinc.com URL-based calls), but there seems to be no way to export those images. There isn't an option to right-click to save the image, nor does copy/paste seem to work.

Are images generated from =image(url) useless besides visual presentation? Perhaps I'll need to create a script to accomplish something like this…

Update: I've also tried the Download As > Web page command, but as others have found out, the =image(url) function doesn't place the returned images in the resources folder. I believe only images place via Insert > Image do that.

Best Answer

You can use the HYPERLINK function. Then you can click the image, and see it in a new tab. For the you tab you can save / download / export or whatever you want.

Example Function

=HYPERLINK("http://www.abc.net.au/news/image/7650580-3x2-340x227.jpg",IMAGE("http://www.abc.net.au/news/image/7650580-3x2-340x227.jpg"))

Related Solutions

Google-sheets – How should I automate graph generation and image output in Google Sheets

Yes, you can do all of this with Google Apps Script, though it's a pretty large project that arguably should not be someone's first project with GAS. Here is an overview of methods involved, with pointers to documentation.

Select a range of cells in a row: getRange.
Create a graph based on that range of cells including key and labels: see Embedded Chart creation, Setting chart type, and customizations such as EmbeddedBarChartBuilder.
Save image file of that graph in a public location. The only place you can save is Google Drive, as follows:

 var sheet = SpreadsheetApp.getActiveSheet();  // grabs active sheet
 var chart = sheet.getCharts()[0];   // grabs the first chart on the sheet
 var file = DriveApp.createFile(chart.getBlob());   // saves it as a file

You get a PNG file named chart.png; this file can be renamed with setName and made public as follows:

file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

Insert link to image file in sheet where cells are located. Use getUrl to get the URL of the file. Add it to the sheet with setValue. Example:

sheet.getRange(5, 3).setValue(file.getUrl());   // automatically linked

You can make the link look fancy (with custom text) using setFormula('=hyperlink('+file.getUrl()+','+ your_text + ')') instead of setValue.

Repeat on next row until blank. Sure, that's what for and while loops are for, among other things such as getDataRange

Google Sheets – Fixing Formulas That Won’t Execute

It's a known issue that spreadsheets could become broken in such way that they can't be fixed in any way.

If you can't open and access the spreadsheet data anymore, then you should contact Google Sheets Support. Also you could try to use Google Apps Script or the Google Sheets API try to recover the data and formulas.

If you can open and access the spreadsheet data you could try the following:

Change spreadsheet settings like Regional and time-zone settings. Wait few minutes and open again the spreadsheet to check if it's working fine after that.
Restore an early version of the spreadsheet from the version history
Make a copy of the spreadsheet
Copy one sheet at a time to another spreadsheet
Download the data by using a CSV, XLXS, or other file format.

If any of the above work for you, please left a comment and share what worked for you.

References

Best Answer

Related Solutions

Google-sheets – How should I automate graph generation and image output in Google Sheets

Google Sheets – Fixing Formulas That Won’t Execute

Related Topic