Google images to Excel and vice versa
stuff
December 7th 2014 (4 years ago)
All my coworkers uses either Excel or Google Spreadsheets, which unfortunately sometimes makes a lot of problems – for example with images, Google Spreadsheets lets you put image link into the cell and resize it with the resize of the cell (with/without keeping aspect ratio), unfortunately, Excel does not allow this.
We also work a lot with Chinese partners, unfortunately for us – Google services are unavailable there and here’s the problem – when my coworkers needs to prepare an excel sheet (with pics) from Google Spreadsheets it was really time consuming and boring process, now most of you would think this should be fairly easy, just click “export” and that’s it – it isn’t, Google Spreadsheet do do not positions the images properly, all are stacked on first column, which makes them unreadable.

Solution

First of all, if in Google Spreadsheets you use “Insert -> Image” command for pictures and then want to export it to Excel You’re pretty much f*cked and i can’t help you there…BUT if you use IMAGE() (documentation here -> Inserting an image inside a cell in a Spreadsheet) then it should be a lot easier.

If You use IMAGE()

1. Export Google Spreadsheet to excel
2. open exported spreadsheet
3. With Google Spreadsheets, your images will be in “IMAGE()” function and in excel you will get and error – remove the “IMAGE()”, but leave the links (example: you have in cell: IMAGE(“http://site.com/image001.jpg”) after removing you should only have “http://site.com/image001.jpg”)
4. Prepare the proper height and width of cells you want for the images (also edit the macro from point 4 if needed)
5. From menu select Developer -> Visual Basic, select the proper workbook and create a macro:
Sub Images()
For Each cel In Selection
  If Not IsEmpty(cel.Value) Then
    ActiveSheet.Pictures.Insert(cel.Value).Select # take this one, for loading pics EXTERNALLY
    Application.ActiveSheet.Shapes.AddPicture(cel.Value, False, True, 1, 1, 100, 100).Select # take this one, for EMBEDDING pics to the excel document
    With Selection
      .Top = cel.Top
      .Left = cel.Left
      .ShapeRange.LockAspectRatio = msoFalse
      .ShapeRange.Height = 100 #change this height to your needs 
      .ShapeRange.Width = 80 #change this width to your needs
    End With
  End If
Next cel
End Sub

6. Select all cells you prepared in point 3 and use Macro on them – this will insert images ABOVE the cells with the links, delete links (or leave them if needed) – voila! You just imported all images to excell list.
7. There is still a problem, that pictures are not binded to width/height of the cell, select all pics, right click, go to Size and properties, then Poperties -> Move and size with cells.

The solution is not ideal but it works and since i showed it to my coworkers, exporting to MS excell from Google Spreadsheet stopped being such a pain.

Bonus

Sometimes, we have an Excel lists with image links (only links), that should be imported quickly to Google Spreadsheet document, this macro take the link and put it in proper Google Spreadsheet function, then you can simply copy/paste the outcome to Google Spreadsheet:
Sub GoogleIMAGE()
For Each cel In Selection
  If Not IsEmpty(cel.Value) Then
    Link = cel.Value
    cel.Value = "=IMAGE(" + Chr(34) + Link + Chr(34) + ")"
  End If
Next cel
End Sub