2

I think it's for experts. There it is, step by step:

  • I created a Google Spreadsheet
  • The following script is running on it:

/*Updated and maintain by internetgeeks.org*/ 
function onEdit(event) 
{ 
  var timezone = "GMT-3"; var timestamp_format = "dd-MM HH:mm"; // Timestamp Format. 

  if (updateColName = "Nº Cotação") {
  var sheet = event.source.getSheetByName('Cotações'); //Name of the sheet where you want to run this script.
  var updateColName = "Nº Cotação"; var timeStampColName = "Data proposta"; 
  var actRng = event.source.getActiveRange(); 
  var editColumn = actRng.getColumn(); 
  var index = actRng.getRowIndex(); 
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
  var dateCol = headers[0].indexOf(timeStampColName); var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1; 
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself! 
  var cell = sheet.getRange(index, dateCol + 1); 
  var date = Utilities.formatDate(new Date(), timezone, timestamp_format); cell.setValue(date);}

  if (updateColName = "Resposta ao Cliente") {
  var sheet = event.source.getSheetByName('Cotações'); //Name of the sheet where you want to run this script.
  var updateColName = "Resposta ao Cliente"; var timeStampColName = "Contatado"; 
  var actRng = event.source.getActiveRange(); 
  var editColumn = actRng.getColumn(); 
  var index = actRng.getRowIndex(); 
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
  var dateCol = headers[0].indexOf(timeStampColName); var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1; 
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself! 
  var cell = sheet.getRange(index, dateCol + 1); 
  var date = Utilities.formatDate(new Date(), timezone, timestamp_format); cell.setValue(date);}

  } 
} 
}
  • This spreadsheet is shared with other users, as editors
  • Some intervals are restrict for some editors

THE ISSUE

  • When the Owner updates the two described columns (see the above code), the script works fine

  • When shared editors modify something, just the second condition (if) of the script is working

Can someone help me on this?

Is there a problem with the script, maybe? Why does it works properly just on the spreadsheet owner?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Daniel Buchmann
  • 21
  • 2
  • Looks that `updatedColName` is `undefined`. See http://stackoverflow.com/q/16089041/1595451 – Rubén Aug 20 '16 at 21:28

1 Answers1

0

Instead of using single '='
Use double '=='

Change the following statement in your code:

if (updateColName = "Nº Cotação")

To:

if (updateColName == "Nº Cotação")

and also change the following:

if (updateColName = "Resposta ao Cliente")

To:

if (updateColName == "Resposta ao Cliente")

UPDATE:
Here is the complete script code which should work as per your requirement: 

function onEdit(event) {
  var actRng = event.source.getActiveRange(); 
  var actSheet = actRng.getSheet();
  if( actSheet.getName() == 'Cotações' && actRng.getRowIndex() > 1 ) {
    var updateColName = actSheet.getRange(1,actRng.getColumn()).getValue();
    var headers = actSheet.getRange(1, 1, 1, actSheet.getLastColumn()).getValues();
    var date = Utilities.formatDate(new Date(),'GMT-3','dd-MM HH:mm');
    if( updateColName == 'Nº Cotação' ) {
      var dateCol = headers[0].indexOf('Data proposta');
      actSheet.getRange(actRng.getRowIndex(),dateCol+1).setValue(date);
    }
    else if( updateColName == 'Resposta ao Cliente' ) {
      var dateCol = headers[0].indexOf('Contatado');
      actSheet.getRange(actRng.getRowIndex(),dateCol+1).setValue(date);
    }
  }
};
Kishan
  • 1,630
  • 13
  • 19