CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Malcolm Sokol
Malcolm Sokol

Reputation: 21

Export images from Google Sheets

I have a Google Sheets sheet that creates QR images from the Google charts API using the IMAGE function that takes a URL to encode as an argument. I would like to export the created images to an image file, say a JPG. Problem is Sheets does not recognize the cell content as an image so SHIFT + Right click does not show the Save Image As in the context menu. Any ideas on how to export all of these to separate files.

Upvotes: 2

Views: 4166

Answers (2)

Vinayak
Vinayak

Reputation: 776

Download the sheet in Microsoft Excel .xlsx format by going to the File > Download menu.

Then open the .xlsx file in your computer using any unzip program (e.g. 7z) and navigate to ../xl/media in the zip file. All the images would be there - extract where you want them.

Upvotes: 4

RemcoE33
RemcoE33

Reputation: 1610

This will fit your needs:

  1. Tools -> Script editor
  2. Clear the little code you see
  3. Paste the code from below
  4. Change sheetname (now Data) to your needs
  5. sheet.getRange(2,1, ..... This assumes your images starting at row 2 in the first column (A). Change this to fit your needs.
  6. Changed the folder id to the folder you want the images. (found in url)
  7. Hit run -> Give permission (once)

Look at the folder.

The script:

function isImageToImage(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Data');
  const formulas = sheet.getRange(2,1, sheet.getLastRow()-1).getFormulas().flat();
  const urls = formulas.map(formula => {
    return /\"(.*?)\"/gmi.exec(formula)[1]
  })

  
  const folder = DriveApp.getFolderById('1QVo_pr_Wo6xxxxxxxxx9Yx')

  urls.forEach(url => {
    console.log(url)
    const blob = UrlFetchApp.fetch(url).getBlob();
    folder.createFile(blob)
  })

}

Upvotes: 3

Related Questions