Google App Script - Return Google Sheet Column Header of the Active Cell and a specific column value of the row where change is made

Question

I am writing to find ways to capture the Google Sheet column headers and a specific column value for the given row.

I have a code where I am recording a changelog into a new sheet. See below:


// This script records changes to the spreadsheet on a "Changelog" sheet.
// The changelog includes these columns:
// "Timestamp", "Sheet", "Cell", "Type", "Old Value", "New Value", "User"
// Users are logged by email address.
// Source 1: https://productforums.google.com/d/topic/docs/az365_ypIV0
// Source 2: https://productforums.google.com/forum/#!topic/docs/AI9OxbOtvWE
// Source 3: http://eyana.me/create-a-simple-changelog-using-google-apps-scripts

function onEdit(e) {  
  var changelogSheetName = "Changelog";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = SpreadsheetApp.getActiveRange();
  var timestamp = new Date();
  var currentSheet = ss.getActiveSheet();
  var currentSheetName = currentSheet.getName();
  var HRISmaster = "HRIS Master";
  var previousValue = e.oldValue;
  var newValue = cell.getValue();
  var typeChange = "Change";


  // if it is the changelog sheet that is being edited, do not record the change to avoid recursion
  if (currentSheetName == changelogSheetName || currentSheetName !== HRISmaster) return;
  var changelogSheet = ss.getSheetByName(changelogSheetName);

  if (changelogSheet == null) {

    // 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(currentSheet);    
    changelogSheet.getRange('A1:G1').setBackground('#E0E0E0');
    changelogSheet.appendRow(["Timestamp", "Sheet", "Cell", "Type", "Old Value", "New Value", "User"]);
    changelogSheet.deleteColumns(8,19);
    changelogSheet.setFrozenRows(1);
    changelogSheet.setColumnWidth(1, 170);
    changelogSheet.setColumnWidth(7, 170);
    changelogSheet.protect();
  }


  var user = Session.getEffectiveUser().getEmail();

  if (previousValue == null){
    typeChange = "Set";
  } else if (newValue == "") {
    typeChange = "Unset";  
  }

  changelogSheet.appendRow([timestamp, currentSheetName, cell.getA1Notation(), typeChange, previousValue, newValue, user]);
}

Now I need to figure a way out to grab the column header and not the cell reference where the change is made and the column value where I have some IDs stored in the same sheet.

I have prepared a sample sheet with dummy data which is exactly the same as the real data I have. The sheet can be found here https://docs.google.com/spreadsheets/d/1sMthpSG-7L-uv7XssQ9UO2FU2o385aekEwhHmb9-1Lk/edit?usp=sharing

I have added an additional sheet (Need this type of Changelog) that shows the kind of changelog I need. Can someone please help.

Best Regards, Syed H

roma roma · Accepted Answer · 2020-05-26T04:25:17

Why not extract them directly using range location?

var field = currentSheetName.getRange(1, cell.getColumn()).getValue();
var id = currentSheetName.getRange(cell.getRow(), 1).getValue();

changelogSheet.appendRow([timestamp, field, id, currentSheetName, cell.getA1Notation(), typeChange, previousValue, newValue, user]);

Google App Script - Return Google Sheet Column Header of the Active Cell and a specific column value of the row where change is made

1 Answers