Google spreadheets get image filename instead of URL shared link

Hi, ODK community
I have been using Aggregate platform for many years now and I have just transited to the Google-sheets, Drive platform. Which appeared more easy to setup.
Now I am facing an important issue about image filename.
When the forms that include photos are submitted to the spreadsheet, the spreadsheet directly shows an URL that redirect to the google drive folder where are store the uploaded images.
When I download the images and export the spreadsheet in excel format to a local computer, I can't figure out how to link the pictures with the records.
The downloaded image filename don't contain the URL Link code (ie : https://drive.google.com/open?id=1FveBX8xoBLPw52IuCigDHE1f26MkHYa5), and the downloaded excel sheet don't contain the image filename (ie :1539331386110.jpg)

If someone in the community can highlight me on how to solve this issue, it will be very useful, as I need to download thousand of images.
Thanks in advance

Hi @Souligno

some time ago we used hyperlinks with file names but users complained about it Data comes wrapped in excel formula so we decided to remove that feature and use just regular links.

Looks as if in your case hyperlinks could be better?

Hi Grzegorz ,
Thanks for your reply, I believe that it would be better to use the hyperlink.
But now, in my actual case what options I have to get the image filenames (.jpg)
Would not it be better to have 2 columns, one with shared link and an other with the filename ?
The funny part of it (i.e: Screenshot ), is that some time the filename appear , I thing is a small bug because it appear once a while.
I found that you have edit a script this post :script
I don't think it will works in my case. Maybe we should edit a new script ??
Thanks

2018-10-16%20(2)

That's strange. It's not a hyperlink right just a text?

So what would be better for you two columns or hyperlinks? I'm asking because maybe we can think abot adding a new setting in ODK Collect to solve your problem.

Hi,
Yes just a text.

Well the Idea is when we export the data and image offline. I just need a way to link my data with offline images and I believe that the simple way is to have a columns with image filenames. So having an extra column with image filenames only , it will be perfect for me.
Thanks,

Hi Grzesiek
Can you suggest me a solution for my current situation ?
What option I have now to link the URL/record with the photo when I have downloaded the photos for offline use.
Thanks

Rather than start a new topic, I am reviving this thread as I can't see that it has been resolved.

I have a similar issue - I have collected data on the phone and uploaded it to Google Sheets. This appeared to be a really useful way of collating data from a small team rather than using Aggregate or Central (I am notoriously useless at installing and configuring the server!)

However, when I download the spreadsheet I only have a hyperlink, which does not allow me to access the images directly, or the image names. I have found that the images are stored in a separate folder and have the 'full path' from my phone as their name i.e. storage_emulated_0_odk_instances_[instance_name]_[image_name]. This makes it hard to integrate with data imported from Briefcase (where the cell contents refer to the image name and relative path, e.g. media/[image_name]). However I can batch rename them to remove the full path...

I have tried using the google sheets script mentioned above but this doesn't appear to give me access to the image name (maybe it's not even intended to do so now I study the script itself!).

Essentially I want to be able to use my data offline, so exporting to excel / csv (with image name column that matches the downloaded images). I can then visualise / analyse the data locally when I have no internet connection (common). I think this is the same as @Souligno but I can't see a solution here or on the related thread.

While I'm at it, it would be really useful for Google Sheets to store location data in the same format as Briefcase exports it - again merging data is more of a hassle than I expected... Sheets uses one column Y,X (a Google convention or just Lat, Long?) whereas Briefcase exports 2 columns [field]-Latitude and [field]-longitude.

Many thanks

Hi Seewhy,

I didn't really find a proper way, but alternatively I was using Files Cabinet by Awesome table add-ons for Google sheets.
The add-ons permit you to list all you files in a specified folder giving you unique google ID file and Filename. With this information I was able to link my ODK photos with my data using ID as the master key between the submission and the file list sheets.
The Google ID is embedded in the URL link file.
I hope it will be useful for you.
Kind regards.

1 Like

Hi @Souligno,
Thanks for your reply.
I can see that this would be a way to deal with it, but means another 'dependency' rather than being part of ODK - it would be much more useful (in my opinion) to include the filename within the spreadsheet when ODK Collect sends the data, as this would be updated whenever new records are submitted.

Although it doesn't solve my problem I found a useful way of show the images within the spreadsheet using a formula in an additional column (again, not a solution, but a helpful measure):

=IMAGE(SUBSTITUTE([D2],"https://drive.google.com/open?id=","https://docs.google.com/uc?export=download&id="))

In case it helps, this formula changes the hyperlink (SUBSTITUTE) from "open?id=" to "/uc?export=download&id=", which loads the file rather than opens a new window, and the IMAGE part does what you might imagine and shows an image rather than the hyperlink.

I found the relevant information from this link: https://nodariko.com/odk-with-google-spreadsheet-and-wordpress/ and adapted it for this purpose. I'm guessing this might be useful if you were, for example wanting to link the data to google maps...

Thanks for your help - hoping that someone from the ODK team can help with a direct solution.
Kind regards

I am in the same situation where you were in Oct'18.
Could you please help me to link my photos to give unique ID and file name to ODK photos with the data. As you mentioned I got files cabinet.

Requesting you to give a step by step instruction to what you did.

Thanks in advance.
Saneesh