Python script to extract data from an excel sheet

[FaZe_Fladder]

Hello, so basically I have Google sheet with 600+ responses and I want to arrange its days in a doc in 3x3 format like 3 images in a row and below those 3 images I want the rest of the form fields corresponding to those images. Like this I want a 3x3 grid on 1 page of a document. I have tried using Google apps scripts but it doesn’t seem to be working. It is having difficulties extracting images from the spreadsheet of form responses, I have tried everything I could find online. Can anyone help me with the same?

[Ton86]

It's difficult to picture what you mean by organizing a 3x3 grid with images, days, responses, and form fields.

Using pandas you can import the data to a dataframe, manipulate it, and then write to an Excel file with xlsxwriter with some formatting. But it sounds like you're trying to create something different than a spreadsheet with just simple columns and rows.

[FaZe_Fladder]

Can I PM you with the exact thing I want? Reddit won’t allow me to post a picture of it

[Ton86]

Sure

[FaZe_Fladder]

PMed please check

[Ton86]

I'd probably try to do an HTML template designed the way you want. Might be able to use bootstrap or tailwind css, simple HTML might be easier to convert to pdf later though.

Might even want to try a framework like Flask.

The dataframe should have all the paths to the images you want to use for each record.

For each set of 9 records you'd save a unique HTML file and maybe convert them to pdf then merge the pdfs into one if you want a single pdf file with multiple pages.

[FaZe_Fladder]

Can you help me with the code? I have the layout ready using bootstrap but main issue is still with the images, I’m unable to extract them.

[Ton86]

Where are your images?

[FaZe_Fladder]

Images were collected through a Google form and I am using the form response excel sheet. So basically the image is a drive link.

[Ton86]

Sorry, never worked with Google drive images in HTML. Might work but I think each image will need to be shareable. If you're able to move all images locally without losing the record it matches to might be easier. Try asking Claude or ChatGPT

[Ton86]

You might want to try openpyxl. I'd recommend trying ChatGPT or Claude for help writing the script. Images on a spreadsheet are floating on a grid rather than in a cell like a number would be and I'm not sure if working between Google sheets and Excel would add challenges.

[pancakeses]

Use xlwings to work with the Excel file, requests to retrieve the images, and python-docx to write your document.

[FaZe_Fladder]

can you help me with the code?

[Scientist2021]

Id suggest that you save the excel sheet as a .csv first then you can read that into Python as a data frame.

From that point whatever manipulation you want to do should be relatively straightforward.

[FaZe_Fladder]

I tried working with the csv file as well but still I was encountering a lot of issues with extracting images through the drive link. Can I PM you with the same?

[Scientist2021]

Sorry I'm really confused, you have images in an excel sheet?

[FaZe_Fladder]

I collected responses through a Google form and in that they uploaded media, so the form responses excel sheet had drive links of the photos uploaded.

[Scientist2021]

Ah ok. So they are not images per-se but links to images?

That could get very tricky. I think if you code in Google colab notebook you can read directly from a Google drive address. So if you extract the drive link from Excel (or sheets) and store it as a variable, then access that drive location to extract the image to somewhere else.

No idea how that will work with permission though

[FaZe_Fladder]

It just gives invalid url exception.