0

I have a spreadsheet shared with me.

It has columns A-K, where K column contains binary image, that has directly pasted there by CTRL+V, most probably. When I set cursor to that cell, there is no text in the formula window. The file exported manually has more than 150Mb.

I need to extract all that values from that spreadsheet directly using API or some alternative with Python script. I don't care about the details, it could be both downloading file to .xlsx and processing or extracting info directly from the spreadsheet.

Which extraction techniques I have tried so far:

  • Requests. Doesn't work, it downloads 4kb html page or so instead of the sheet.
  • Non-google libraries for extraction. Don't work as well, probably due to file size. It's quite big, 150+ Mb.
  • Google Drive API. As I understand it has limiter features to extract and allows 10mb max size.
  • Google Spreadsheet API. Works well, but when I call
    result = sheet.values().get(spreadsheetId=SPREADSHEET_ID,
                                       range='Brands!A2:K',
                                       valueRenderOption='UNFORMATTED_VALUE',).execute()

It returns all the columns without K. I played with parameters but it just doesn't return binary data. Is there a way to extract information from script?

sevatster
  • 133
  • 1
  • 3
  • 11
  • When you say binary image, do you mean this type of [images](https://en.wikipedia.org/wiki/Binary_image), this function of Google Sheets ['Image In Cell'](https://support.google.com/docs/answer/9224754) or the raw binary content of the image? – Emel Nov 12 '21 at 09:35
  • @Emel I suppose it's Image in Cell, there is no formula in formulas bar when I highlight such cells, only a small preview. But image is quite big itself. – sevatster Nov 12 '21 at 11:32
  • Unfortunately that feature is not yet available. In this [link](https://stackoverflow.com/questions/56733340/how-to-access-new-in-cell-image-from-google-apps-script) you can find several workarounds. If you want to follow the status of the report on Issue Tracker, follow this [link](https://issuetracker.google.com/issues/129384672). – Emel Nov 12 '21 at 13:22
  • Do [this](https://stackoverflow.com/questions/69939620/how-to-export-binary-image-from-google-spreadsheets-using-api) answer solve your problem? – Emel Nov 12 '21 at 13:43
  • I saw those workarounds before and they weren't helpful. So the feature doesn't exist. Is is possible to export that sheet via API to xlsx? – sevatster Nov 12 '21 at 15:39
  • Try using `files.export` API. Check this [answer](https://stackoverflow.com/questions/11619805/using-the-google-drive-api-to-download-a-spreadsheet-in-csv-format/51235960#51235960) for more references. – Emel Nov 12 '21 at 15:44

0 Answers0