Tuesday, 25 January 2022

Archive Sheet data using tickboxes

The following Google Apps Script is designed to move a row of data from one Google Sheet tab to another when a tickbox has been selected in a given column. The tool was developed to help colleagues with automatically archiving data that had been actioned.

The tool uses an 'onEdit(e) simple trigger' to constantly monitor the Google Sheet for any changes in values. Is it specifically targeted at a tickbox-column only so that it does not archive rows of data on every edit of the Google Sheet.

Archive a row of data by ticking a box
Archive a row of data by ticking a box

Instructions

I have tried to make the tool somewhat universal so you could apply it to an existing Google Sheet, but for this you will need to copy out the Apps Script code, paste it into your file and make 3 tweaks:

  1. Open your copy of the Google Sheet and go to 'Tools' then 'Script editor'.
  2. Highlight and copy all of the code displayed - right from the top to the very last bracket.
  3. Open your Google Sheet where you want this archiving ability to be added and go to 'Tools' and 'Script editor'.
  4. Highlight and delete everything in there currently, then paste in the code you copied in step 2.
  5. Now update the 3 items in the top section of the code to match your Google Sheet.
  6. Finally click the Save icon and close the Script editor. Now you are good to go!

 

Caveats

This tool is unable to move over cell comments/notes.

It will also convert the original tickbox value from the source tab to 'TRUE' or 'FALSE' on the destination tab - so whilst it will not lose the data it will lose the format of a tickbox.


Download

Archive Sheet data using tickboxes download here (please use 'File' > 'Make a copy' for your own version).

2 comments: