CODEWITHSUNDEEP
mergegoogle-sheetstimestampchangelog
Ghita Dan
Ghita Dan

Reputation: 11

Combine two scripts into one Google Sheet

I got this two scripts from different posts, and I'm having trouble merging them.

The short script inserts a timestamp if the status column changes in Sheet1 and the other script makes a change log that logs the changes made. Is it possible to merge those two into one, and maybe edit the changelog to just record when the status column is edited?

Thanks in advance!

Google Spreadsheet: https://docs.google.com/spreadsheets/d/1m61JTFIacGifU-UD9yIzXdhAYKiiZHN3PooyifRHVf0/edit?usp=sharing

TimeStamp script: 

`function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() == "Sheet1") {
    var r = e.source.getActiveRange();
    if (r.getColumn() == 3) {
      sheet.getRange(r.getRow(),r.getColumn()+1).setValue(new Date());
    }
  }
}

`

ChangeLog Script:

 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()]);
}

Upvotes: 1

Views: 601

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18717

Try to rename your functions and add only one onEdit:

function onEdit(e) {
  myFunction1(e); // call function #1
  myFunction2(); // call function #2
}

Upvotes: 1

Related Questions