What is this?

The data curation script is a simple google script that runs in the context of any google spreadsheet file and performs a couple of basic tasks.

  1. Fixed edits:
    1. Clean all the section prefixes inherited from ONA form (eg section8./section8.2/section8.2.3/.....)
    2. Transform date/time format
    3. Replace true/false to yes no
  2. Dynamic edits
    1. Apply all the changes defined in the replacementDictionary
    2. Delete the columns defined in columnsFilter


How does it work?

These are the necessary steps to run the script.

  • Open the script (it's a file inside a google drive folder currently called "Custom LabMap Data Curation")
  • Click on Run in the menu and select "Test as add-on"
  • Then select the spreadsheet in which you want to run the script clicking in "Select Doc", and once it's selected click Save.
  • The file will be listed above, just select it and click Test

Once in the spreadsheet the script will be found under the add-ons menu.

  • If you click on Data curation a small form will be displayed in the right sidebar. 
  • All the fields need to be completed to run the script
  • If there are no replacementDictionary and/or columnsFilter sheets they can be copied from the available copies om the "recourcesforCuration" folder in google drive.

Details on replacementDictionary and columnsFIlter

The replacementDictionary has two columns the one in the left correspond to the original values that want to be changed and the right column correspond to the new values.

The columnsFilter runs first so the columns listed in this sheet should have their original name to be deleted.

The replacementDictionary runs at the end so before confirming the left column values you need to consider the fixed edits done before.

Both sheets work with an exact match, it means that the replacements and deletions will be done only when the text in the left column equals the whole content of a cell.


This video shows the step by step process to run the script 


Scope and potentiality

This script was initially written by InSTEDD to expedite the process but the code is totally open to any change, any developer or even a google power user can add a few lines to the script and get new functionalities.