Wednesday 19 December 2018

Archive sheet data

The following Google Apps Script code is designed to move a row of data from one sheet to another - effectively 'archiving' it. Understandably this task can be performed by copying/pasting in a spreadsheet, but with a tool like this it can all be done at the click of a button.

The row number is acquired via 'getActiveCell / getRow' along with 'getLastColumn', thus allowing for the full row of data to be acquired as an array. This can now be appended to the destination sheet (so it will always appear below the last row of data) and deleted from the current source sheet.

The 'onOpen' function automatically creates a menu item called 'Custom Menu' when the spreadsheet is opened. It then contains an option called 'Archive Data' which runs this 'archiveData' function hence dramatically outperforming manual copying/pasting.

Archive Sheet Data.xlsx

2 comments:

  1. Thank you, very helpful!

    is it possible to archive only the rows that have a certain value (eg. TRUE) in the last row?

    ReplyDelete
    Replies
    1. Hi

      Yes absolutely. You would need to wrap it all in an 'if' statement. So get that column first (eg 'H'):
      var colH = sourceSheet.getRange(currentRow, 7).getValue()

      Then do the 'if' bit:
      if (colH = 'TRUE') {
      ... do append and delete row stuff ...
      }

      Kind regards
      Phil

      Delete