How to specify the spreadsheet in which a google api code should work?

0
votes

My google sheets document has multiple sheets (tabs). How do I specify the spreadsheet in which code should work? I am using the last code from the post below to collect the date and user who changes a particular cell, but the code works for all spreadsheets (tabs) in my document.

Is It Possible To Write Username Of Editor To Cell In Google Docs Spreadsheet

I would like it to work on just one spreadsheet: "Test"

I tried this but to no avail:

var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");

    function getOwnName(){

  var email = Session.getActiveUser().getEmail();
  //Browser.msgBox("getOwnName");
  //var self = ContactsApp.getContact(email);
  var self = false; 

  // If user has themselves in their contacts, return their name
  if (self) {
    // Prefer given name, if that's available
    var name = self.getGivenName();
    // But we will settle for the full name
    if (!name) 
    {
      name = self.getFullName();
    }
    //Browser.msgBox(name);
    return name;
  }
  // If they don't have themselves in Contacts, return the bald userName.
  else {
    var userName = Session.getActiveUser().getEmail();
    //Browser.msgBox(userName);
    return userName;
  }
}

function onEdit() {
    var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
    var r = s.getActiveCell();
    if( r.getColumn() == 14 ) //checks the column
    { 
      var nextCell = r.offset(0, 1);      
        nextCell.setValue(new Date());
      //Browser.msgBox("hey!");
      //s.setActiveSelection(p + "15");
      //show();
      //SpecialonOpen();
      var nCell = r.offset(0,2);
      //Browser.msgBox("olah:" + getOwnName());
    nCell.setValue(getOwnName());
    }
}
1
google-apps-scriptgoogle-sheets-api

1 Answers

1
votes

The active cell is the one that is being edited, no matter what the sheet is.

If you want to stick to getActiveCell(), you need to implement an if statement verifying if you are in the right sheet:

function onEdit() {
  if(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName()=="Test")
  {
   var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
   var r = s.getActiveCell();
   if( r.getColumn() == 14 ) //checks the column
    { 
      ...

A different elegant solution would be to work with e.range:

function onEdit(e) {
  if(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName()=="Test")
  {
   var r = e.range;
   if( r.getColumn() == 14 ) //checks the column
    { 
     ...