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 |
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:
- Open your copy of the Google Sheet and go to 'Tools' then 'Script editor'.
- Highlight and copy all of the code displayed - right from the top to the very last bracket.
- Open your Google Sheet where you want this archiving ability to be added and go to 'Tools' and 'Script editor'.
- Highlight and delete everything in there currently, then paste in the code you copied in step 2.
- Now update the 3 items in the top section of the code to match your Google Sheet.
- 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).
This tool is super useful thank you very much for sharing it!
ReplyDeleteThank you!
DeleteKind regards
Phil