ODK is a popular tool for offline data collection with a thriving user community. Like a number of other offline data collection tools, it uses the XLSForm standard for authoring forms. While convenient to author and exchange, authoring the forms in Excel means each iteration of the form must be uploaded to the ODK Central server that distributes forms to mobile data collection applications, such as ODK Collect.
In part since ODK Central consists of a Node backend and Vue frontend, it includes a robust API that supports all functions supported by the server. Using this API along with the Google Sheets API, it is possible to fully automate the process of exporting a draft XLSForm from Google Sheets and publishing it as a new version of the form in ODK Central. This may be useful during development of a form to shorten the feedback cycle between making a change and testing that change on a real device.
As a proof of concept, I created a gist on GitHub with the code you can use as a starting point for the integration for your form.
To add this automation to an XLSForm in Google Sheets:
- Navigate to the spreadsheet and select the Extensions -> AppsScript menu.
- Copy the code from GitHub into the AppScript code editor.
- Back in the spreadsheet, you can create a button to publish the form by selecting Insert -> Drawing, creating a drawing with the shape and text that you would like, and then clicking the Save and Close button.
- To assign a script to the new button, click on the button, select the three dot menu on the right side of the drawing, and click Assign Script. In the box that appears, type "main" (the main function in the script you added) and then click OK.
- Now, you should be able to run the script by clicking your newly created button. The first time it runs, you will need to grant the necessary permissions. You will also be prompted for the base URL for ODK Central, project ID, form ID, and ODK Central email/password. It is recommended to create a less-privileged service user with access only to the project needed to publish the form. The email and password are not saved; instead, the script keeps a bearer token in its script properties which is valid for up to 24 hours (similar to a login to ODK Central itself). After 24 hours, you will be prompted again for your email address and password.
- If you need to move or edit the button in the future, note that you will need to right click on it first as clicking it will simply cause the script to run.
Good luck, and please feel free to leave any questions or comments below.
