As of March 2023 'Archive Sheet Data' is now available as a Google Workspace Marketplace Add-on.
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).
As of March 2023 'Archive Sheet Data' is now available as a Google Workspace Marketplace Add-on.
This tool is super useful thank you very much for sharing it!
ReplyDeleteThank you!
DeleteKind regards
Phil
Hi Phil
ReplyDeleteMany thanks for that. Just one comment. How do I get the script to show the destination sheet after it has done the copying and deleting?
Hi Nic
DeleteI've not done this specifically before but here is some vanilla code you could apply:
function jumpByRange() {
var spreadsheet= SpreadsheetApp.getActiveSpreadsheet();
var range = spreadsheet.getRange('Sheet2!A1');
range.activateAsCurrentCell();
}
Kind regards
Phil