The task of this project was to use ODK with WordPress to collect data for one of the local company interesting in the market situation, their competitors distribution in the market and arrangements of their products in the refrigerators, this was a key factor of cooperation but besides this we were able o add some extra tasks in the form to collect some more information.

So, i created 3 forms but I will talk only about one because the method I used for other forms is the same
this is the information we collect in the forms:

  1. Date – report date
  2. Agent name – reporter
  3. ID – Id of the Device
  4. Phone number – Sim card number currently in the device
  5. District – We have a lot of markets so we decided to split them by territory
  6. market – name of the market where report was created
  7. market location – GPS location of the market
  8. Competitors – you can choose how many competitors are in the particular market
  9. picture 1 – Outside picture of the market
  10. Picture 2 – Before the arrangement of the product
  11. Picture 3 – after the arrangement of the product
  12. And others questions that I will not share due to the company’s private interest

In this post, I will not talk about how I created a form in the XLS form because this easy part and more detailed information can be found on the internet, most interesting part is how I used ODK with WordPress embed data from google spreadsheet to WordPress, I will divide this task by two main subjects:

1. Google Spreadsheet and ODK data

The data in google spreadsheet is just like a database, basically its kind of database, this is how raw data looks in the spreadsheet:

As we can see data here cannot be used as a final result, we need a little bit more informative table where we have only necessary information, in this raw data we have also some other data that comes from the ODK collect which is not really interesting to see in the final report.

there is also one problem, images here are delivered as a link to the google drive so, if we want to see them you have to click on the link and open them in the separate window, this is of course really bad user experience, and goal is to show them in the cell as a thumbnails.

After thinking on this problem I decided to create a new sheet in excel, in the same spreadsheet where I will migrate data from the raw data sheet called “results” (this is default name of the sheet ODK collector uses) in the more user-friendly fashion, shortly this is the final view:

Nothing magical here, most interesting part here is how I was able to transform image link into image thumbnail in the cell, this is how I did it, I used IMAGE formula that inserts an image into the cell, here is how this formula looks:

IMAGE(url, [mode], [height], [width])

it needs URL parameter to the image (Mode, height, and width is optional, you can research this formula if you want to know more), but our links to the images look like this:

https://drive.google.com/open?id=1knq__QTraGEHl-WtW6glyVJ2OxsjWuiJ

unfortunately, this will not work in this formula because we need a direct link to the image file, not image page,  it should be ending on .jpg or .png. after some research I found out that if I use this link structure it will work for me:

https://docs.google.com/uc?export=download&id=1knq__QTraGEHl-WtW6glyVJ2OxsjWuiJ

So, now I just need to replace this:

https://drive.google.com/open?id=

to this:

https://docs.google.com/uc?export=download&id=

for this task I used SUBSTITUTE formula that replaces one string to another in the given data, Here is the final formula:

=IMAGE(SUBSTITUTE(results!Y2,"https://drive.google.com/open?id=","https://docs.google.com/uc?export=download&id="))

On this step, we already have nice excel sheet where searching and browsing information is pretty normal and does not need anything else, the goal of the project was accomplished here but I decided to move forward and my interest was to show all this data in the website. let’s move to the second part

2. Google Spreadsheet and WordPress

This is the questions you will have to deal in this task:
1. website – Raw HTML websites or CMS, since my all time favorite is WordPress I decided to use WordPress
2. Plugins – how can be these data transferred from spreadsheets to website pages? there were several ways but finally, I stopped on this versatile plugin wpDataTables which can be found here https://wpdatatables.com this plugin is simply amazing if you deal with the data in WordPress and probably best plugin when working with data.
In order to supply plugin with the date we need spreadsheet URL and it must be shared, detailed instruction on how this system works you can find in the plugin documentation and I will not repeat it once its already been said :)

WP Lightbox 2 – i used this plugin to enable lightbox for the images in the rows

Password Protected – this plugin to lock the website for the world, only the persons with the password will be able to see the content of the site
3. Theme – I used this theme WP Bootstrap Starter its based on bootstrap and has great style

 

Basically, that’s it, here is the final look of the result:

Other things to consider:

Camera – We use Open Camera with a custom text, timestamp, and GPS stamp embedded into the photo. For those stuck on older Collect versions, it also compresses photos to reduce bandwidth.
Is it slow? – well, we already have 15000 rows in the file but its possible to work with them without any problem, yes its little bit slow but nothing to impossible, however in the WordPress side it needs little bit time to load the data and once its done tables are working very fast

 

Any question? – comments are welcome