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

1 Like

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.

1 Like

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,

1 Like

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

1 Like

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

Hello all, I'm reviving this threath once again since I have the impression there is still no good way to retrieve the actual picture name after the hyperlink version was dropped (for good reasons described in this threath.
I have seen a work-around on stack-overflow but this issue is giving me quite some headeache as I have set up a form for colleagues who are not able to run skipts in Google Drive and the like. Moreover it would need to be re-done after every new upload.

Nevertheless their workflow requires to link both, the data-entry sheet and the pictures to be stored and linked offline.

Are there currently any plans to implement the above discussed option for two columns, one with the URL and one with the actual file-name? This sounds just perfect and I could imagine it would be relatively easy to implement e.g. via the 'parameter' options to have the URL, the picture's name, or both with the default being the current option.

Alternatively: has anyone found a good work-around?

I appreciate any feedback, thanks a lot in advance,
Vincent

Hi @Vinc
Welcome to the ODK forum. We're glad you're here. When you get a chance, please introduce yourself on this forum thread. I'd also encourage you to add a picture as your avatar because it helps build community!

Currently we don't have plans to implement any changes in google drive/sheets integration.

Hi @Grzesiek2010 ,
thanks for your quick response, even though this is quite unfortunate news for them. So if I understand it correctly the only option then would be to use Google Drive Skipts to extract the file names, correct?

Thanks again! If anybode has found a better work-around, I'd appreciate a response.

I'll make sure to introduce myself next.

There is no other option that we would provide so probably yes it's the only thing you can do.

everyone who has this problem still send me a message here so I can try to help, I managed to solve it with a script and some functions in googlesheet, I believe it can help people in the community

1 Like

Hi, can you share the script somehow ?

You do not need any script... just copy the columns with the links to a separate column. Highlight all the links in the the copied column, right click, scroll down to "smart chips" and select "convert to file chips"