Keeping track of columns for onEdit triggers using sheets metadata

Sheets Developer Meta Data – Apps Script advanced service, shows how to use the Sheets Advanced service to work with Sheets DeveloperMetada, and since the resources from this service are a little complicated you may want to use a library (although this example doesn’t, so you can see how to do it in native mode). If you’d rather use the cSAM library (discussed in Sheets Meta Data Advanced service library utilities), it’s at

1wYuya4Vchqa8Ovl0MYSxYwKM8ZjiGFqO5NUo_KzGi-GPMT-HfDEJ6i3k

and the code is available on GitHub or here.

You will of course need to activate the Sheets advanced service first.

Page Content hide

1 simple onEdit trigger

2 Sheets DeveloperMetadata

3 Installable trigger

simple onEdit trigger

As you will know, you can create a trigger to watch out for spreadsheet edits. This example does that and adds a timestamp if a particular column is edited. Here’s the test sheet, and the idea is that when the “municipality” column is edited, the “timestamp” column recevies a timestamp of now.

The trigger code itself is not exceptional, and calling the function onEdit automatically designates it as a simple trigger.

function onEdit(e) {
  
  var targetCol = 7;
  var resultCol = 9;
  var targetSheet = "Airports-play";
  
  // if its an interesting range then add a timestamp
  if (e && e.range && e.range.getColumn() === targetCol && e.range.getSheet().getName() === targetSheet) {
    e.range.offset ( 0, resultCol - targetCol, 1,1).setValue (new Date());
  }
}

But you can see the problem here is that if any columns are inserted or deleted, or a sheet is renamed, it will stop working. A way to solve this would be to use named ranges – but they themselves can be changed or deleted in the spreadsheet UI, which would again cause the function to fail.

Sheets DeveloperMetadata

As you’ve seen in other posts, metadata can keep track of changes in structure, so it would be better to use this to decide whether a column being edited was an interesting column, as well as to keep track of where the timestamp column is. We’ll use the municipalityColumn metadata item created in Sheets Developer Meta Data – Apps Script advanced service to keep track of the target column and add another item – timestampColumn – to keep track of where that column moves to. The batch request for creating that column is as follows

{
      
      // stuff for a column level -- I'll use this one in a later demo
      // CreateDeveloperMetadataRequest
      createDeveloperMetadata:{
        // DeveloperMetaData
        developerMetadata:{
          // DeveloperMetaDataLocation with column scope  
          metadataKey:"timestampColumn",
          metadataValue:JSON.stringify({
            writtenBy:Session.getActiveUser().getEmail(),
            createdAt:new Date().getTime()
          }),
          location:{  
            dimensionRange: {
              sheetId:sheet.getSheetId(),
              dimension:"COLUMNS",
              startIndex:8,             //(column 9)
              endIndex:9                // actually only 1 row (works like .slice)
            }
          },
          visibility:"DOCUMENT"      
        }
      }}

Installable trigger

Because you’ll be using an Advanced Service that needs authorization, you can’t just rename it to onEdit as simple triggers have limited capability. Instead, you have to create a new function, and add it to you your projects triggers as a Spreadsheet/onEdit installable trigger.

Here’s the code – Notice that instead of specifying specific columns and spreadsheet targets in the function, the sheet developer metadata associated with the supplied keys can be used to compare against the range being edited and to figure out which column needs a timestamp and will still work, no matter how the columns and sheets are re-arranged in the meantime.

function onEditSM (e) {
  
  if (e && e.range && e.source) {
      
    // get the current state of the target Column
    var result = doSearch("municipalityColumn");
    
    // get the last in case theres more than 1 (this is why I use a library normally)
    var dim = getDimRange (result);
    if (!dim) throw 'couldnt find meta data for targetColumn';
    
    // finally we have the dimension range 
    if (dim && e.range.getColumn() === dim.startIndex + 1 && e.range.getSheet().getSheetId() === dim.sheetId) {
      // this is the right column as the target
      // now find out what needs to be updated
      var result = doSearch ("timestampColumn");
      var tDim = getDimRange (result);
      if (!tDim) throw 'couldnt find meta data for timestamp column';
      // for debugging e.range.offset(0,3,1,1).setValue(JSON.stringify(tDim));
      
      // add the timestamp
      e.range.offset ( 0 , tDim.startIndex - dim.startIndex , 1,1).setValue(new Date());
      
    }
    
  }
  
  //this is why I use a library - find the latest dimensionRange in a search request
  function doSearch (key) {
    return Sheets.Spreadsheets.DeveloperMetadata.search({
      dataFilters:[{
        developerMetadataLookup: {
          metadataKey: key
        }}]
    }, e.source.getId());
  
  }
  function getDimRange (result) {
  
    var rm = result.matchedDeveloperMetadata;
    var match = rm && rm.length && rm[rm.length-1];
    return match && match.developerMetadata && match.developerMetadata.location && 
      match.developerMetadata.location.dimensionRange;
  
  }
  
}

For more like this, see Google Apps Scripts Snippets