Improve the way we refer media files in Google Sheets

Ideas
,
1

When we upload a filled form to Google Sheets and the form contains media files we put links which don’t contain their media file names. They look like:
https://drive.google.com/open?id=1p6HKVuna8rsKII5ChXbxXaAPOnQoHETT

For users who need to export such filled forms and operate on them locally it might be a problem to refer to downloaded media files.

Sample complaints:

In Collect v1.14 we used hyperlinks which contained file names but it was a problem for other group of users:

so we decided to get back to simple links.

It would be good to find a solution that would work for both groups.

Possible solution:
We could use links by default like we do now but add an option to use hyperlinks as well.
Actually we could do that in two ways:
-a global option in General Settings (I’m not sure where exactly) like Use hyperlinks in Google Sheets
-a local option for each single form like we do specifying submission url:
<submission action="https://example.com/submission" useHyperlinks=”yes” method="post"/>

Users that might be interested in this feature:
@seewhy @Souligno @nodari @Nicola_Pace

2

Hi @Grzesiek2010
Thanks for opening this topic.

I can see the problem of using 'wrapped' hyperlinks for some people.

Would it be logical instead to keep the existing URI to the Drive file and insert an additional column (or vice-versa!) that is simply a plain text value for the filename of the media file (for example, a photo taken with the image widget, 1565778282401.jpg). That way we can identify the file if it is downloaded from Drive and shared in any other way. This would be reasonably consistent with Briefcase, which would export the field as media/1565778282401.jpg (ideally they would be the same format and the same column number in the csv, and the Google Drive link would be at the end, but that's just for my needs!)

At present the media files, when downloaded from Drive, appear to have the full path from the Android device:

_storage_emulated_0_odk_instances_[instance-name]_[filename].jpg

so if they could be uploaded as truncated to just the filename that would facilitate the process of importing to other systems. However, if there are other reasons for keeping the full path, a 'batch convert' of the downloaded files can do that if necessary, so it's merely a suggestion.

This approach would retain the advantages of the existing links, but add functionality without any 'risks' of data corrupting during transfer to other software. Looking at your possible solution:

could perhaps be
<submission action="https://example.com/submission" addPlainMedia=”yes” method="post"/>

or

Add Plain Media Field in the 'General Settings' if the Server is set to Google Sheets

The first option is going to be more reliable for ensuring all enumerators get it right... It would be unfortunate to get some data submitted without the plain text field if the enumerator changed or didn't set that option (and might break the import?)

In my case, I use QGIS with most of my forms and have existing layers that allow me to view the images as geolocated data points. I also produce reports from QGIS that call the images. These break when I try to import the data from Drive (although I've found a way to manage it with some scripting and changes to my layers, but obviously I need to be online at the time to view the images - not always the case!).

Often I am doing fieldwork in areas where I have no internet connection on my field device and I need to download that data daily to a laptop, so I use Briefcase for that (I might be 3 or 4 days on site sometimes with hundreds of data points, so it's risky to leave the data on a single device). Or a colleague does the same. And so I often end up with two or more incompatible datasets from a single form... Not very efficient.

At the same time, formatting the location fields of geopoints to match Briefcase would be really helpful - QGIS uses two fields to calculate the vector point when importing 'delimited text layers' so I have to use a formula to separate the Drive version of the dataset (lat,long in a single field)... Sorry to introduce other issues into this discussion, but hopefully it helps explain the context.

3

I'm creating a separate topic for each GS related issue so the one with gps will be in a separate one.

The problem with such an extra column is that if users had already uploaded some forms to a sheet they would need to create a new one for submissions with the extra column or add it manually to existing sheets. So we can do that but both option, that one in general settings and that option on a form level should say NO by default to avoid problems with uploading. If someone enables it that means their understand the possible consequences which we should describe i the documentation then.

4

OK, thanks - can you tell that I'm keen to get this working :slight_smile: I guess the same issue with extra columns will arise there.

That makes sense for it to default to 'no change'. I can see the problem integrating new and existing data - welcome to my world :slight_smile: I guess I was assuming that there would be backwards compatibility issues to deal with anyway (as there have been with other enhancements in ODK), so having a default that doesn't break the existing dataset is a good thing.

Presumably anyone implementing this via an xml form definition (whether that's eventually using Build, XLSForm or directly) would need to deliberately change / add that, so it becomes likely that if you know this in an option, you might have seen a description of what it does / means. Consequently, I would more strongly favour a setting on a form level rather than device / global level (assuming that is what you meant by General settings?).

Would it break the sheet if the new column were 'appended' on the right-hand end? Does Collect do any validation of the existing columns before appending to the Google Sheet (that's a question rather than a feature request!) - if not then I guess it would just shunt everything sideways from that column? Sorry if these are stupid questions, but it gives you an insight into my lack of understanding what's 'under the bonnet'.

I think that allowing an enumerator to change that setting could cause issues that would be difficult for a survey manager to resolve remotely - when someone gets in touch to say that the form has not uploaded properly, for example, it might be tricky to narrow it down to ask the question "did you change the General Settings option for Google Sheets", or worse, the survey manager finds one or more set of responses in a different format.

On a basic level, I'm not sure that it should be up to the enumerator to decide how the Google Sheets data is uploaded? From experience, some of the folk I've had collecting data should not be in a position to do that, accidentally or on purpose! They're lovely people, don't get me wrong...

5

yes form level and global level in general Settings) it would exactly like submission url does, I mean the value specified on a form level should override the global one.

yes the order must not be changed so if we decided that we add an extra column for media files after the existing one you would need to add such a column manually after every single media column not at the end.

right, a form should be sent but values might be mixed a bit.

every option might be hidden you can do that in Admin Settings before you give anyone such a device to work, so we can do the same in this case.

6

That assumes that I have physical access to the device to set up - this is rarely the case - what tends to happen is I upload the form definition to Google Sheets and share it, the enumerator is in a different part of the country and downloads ODK Collect to their phone or tablet, sets up the app using my excellent instructions (!) gets the blank form, and away they go...

That's why I would be wary about a setting like this in my situation - I rarely get to set the Admin privileges. Maybe a 'here be dragons' warning on the setting screen could be okay, but some folk like playing with dragons... So I guess I'm just doing a risk assessment and thinking that the potential consequences could be serious. Loss of data and loss of face (with reluctant enumerators who thought it was fine to use paper and send it through the post, what is all this new fangled technology anyway?).

7

Any solution of the above mentioned problem

1 Like
8

Hi @Elieabirizk
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!

Nothing has changed in that area so no the problem still exists.

10

(post deleted by author)

11

hello, I saw that your explanation may be able to help me with my forms, but I was in doubt how to use this example you gave, could you please tell me how and where I put this syntax that you put .. you informed that you would have to use but didn't say where or how, .. could it be clearer please, if possible, show in an XML form where I enter this syntax ..( <submission action="https: //example.com/submission" useHyperlinks= ” yes” method="post"/> )
in my case I need to get the photo name along with the link in the same worksheet, which can be in the same column or in another one, but I can't find a solution... The command ( <submission action="https: //example. com/submission" useHyperlinks=” yes” method="post"/> ) it is inserted where in the xml form? Could you be kind enough to teach me with an example how I use it??

1 Like
12

It was just a proposal. I has never been implemented so that useHyperlinks=”yes” option does not exist.