Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Code used for archiving past events in the daily activity log spreadsheet.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Activity Log')
.addItem('Archive Past Events', 'archivePastEvents')
.addToUi();
}
function archivePastEvents() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("Activities");
var archiveSheet = spreadsheet.getSheetByName("Archive");
var numColumns = sheet.getLastColumn();
var firstRow = 2;
var range = sheet.getRange(firstRow, 1, /*numRows=*/sheet.getLastRow(), /*numColumns=*/numColumns);
var values = range.getValues();
var columns = SpreadsheetDB.getColumnNames(sheet);
var indexes = SpreadsheetDB.getColumnIndexes(columns);
var now = moment();
for (var i = values.length - 1; i >= 0; i--) {
var row = values[i];
if (now.isAfter(dateAsMoment(row[indexes["Date"]]), 'day')) {
// Event is in the past.
archiveSheet.insertRowBefore(2);
var newRowRange = archiveSheet.getRange(2, 1, /*numRows=*/1, /*numColumns=*/numColumns);
sheet.getRange(i + firstRow, 1, /*numRows=*/1, /*numColumns=*/numColumns).copyTo(newRowRange);
sheet.deleteRow(i + firstRow);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment