Created
June 12, 2017 20:05
-
-
Save hlecuanda/74cddfc3150dad1dc9266c952ae64194 to your computer and use it in GitHub Desktop.
Log changes to a google spreadsheet (changelog sheet)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function onEdit() { | |
// This script records changes to the spreadsheet on a "Changelog" sheet. | |
// The changelog includes these columns: | |
// "Timestamp", "Sheet name", "Cell address", "Column label", "Row label", "Value entered" | |
// Version 1.1, written by --Hyde, 30 July 2014 | |
// See https://productforums.google.com/d/topic/docs/7CaJ_nYfLnM/discussion | |
// edit the following lines to suit your needs | |
// changes are only recorded from sheets listed below | |
// escape regular expression metacharacters as in \. \$ \+ \* \? \( \) \[ \] | |
// see http://en.wikipedia.org/wiki/Regular_expression | |
// use '.+' to include all sheets | |
var sheetsToWatch = ['outcome overview', 'Sheet1', 'Another sheet']; | |
// name of the sheet where the changelog is stored | |
var changelogSheetName = "Changelog"; | |
var timestamp = new Date(); | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getActiveSheet(); | |
var cell = sheet.getActiveCell(); | |
var sheetName = sheet.getName(); | |
// if it is the changelog sheet that is being edited, do not record the change | |
if (sheetName == changelogSheetName) return; | |
// if the sheet name does not appear in sheetsToWatch, do not record the change | |
var matchFound = false; | |
for (var i = 0; i < sheetsToWatch.length; i++) { | |
if (sheetName.match(sheetsToWatch[i])) matchFound = true; | |
} | |
if (!matchFound) return; | |
var columnLabel = sheet.getRange(/* row 1 */ 1, cell.getColumn()).getValue(); | |
var rowLabel = sheet.getRange(cell.getRow(), /* column A */ 1).getValue(); | |
var changelogSheet = ss.getSheetByName(changelogSheetName); | |
if (!changelogSheet) { | |
// no changelog sheet found, create it as the last sheet in the spreadsheet | |
changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets()); | |
// Utilities.sleep(2000); // give time for the new sheet to render before going back | |
// ss.setActiveSheet(sheet); | |
changelogSheet.appendRow(["Timestamp", "Sheet name", "Cell address", "Column label", "Row label", "Value entered"]); | |
changelogSheet.setFrozenRows(1); | |
} | |
changelogSheet.appendRow([timestamp, sheetName, cell.getA1Notation(), columnLabel, rowLabel, cell.getValue()]); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment