When Toggle is selected "Approved" User email and times stamp of when approved in two separate Cells

[Nathan Mahys]

Hello,

I am new to this whole app script thing. I am having difficulty trying to figure this out. I am trying to make a script that will run when Column A is toiled to "Approved" Then Column D will add users email to that Column on the correct Row and Column E will add time stamp when Column A was toggled to Approved or declined.

I have the first part of the Script for Approved but It only works for the First Row A2.... I need it to work for every row when I add rows to this sheet from a form I have linked to Google Sheets. 

Then I need the same thing to happen when Denied is Picked 

Here is the script I have currently :

function onEdit(e){

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getActiveSheet();

var users = ss.getEditors();

var date = new Date();

if(sheet.getRange('A2:A').getValue() == "Approved" && (sheet.getRange('D2').getValue() == '' || sheet.getRange('E2').getValue() == '') ){

for(i=0; i<users.length; i++){

if(Session.getActiveUser().getEmail() == users[i].getEmail() ){

console.log('i=' + i +'; user name = ' + users[i].getEmail());

sheet.getRange('D2').setValue(users[i].getEmail() );

sheet.getRange('E2').setValue(date);

}

}

}

}

Thank yall for taking the time to look at this....

Here is a Screen Shot of my Google Sheet and what I am trying to do:

[Keith Andersen]

Try this

function onEdit(e){
let ss = SpreadsheetApp.getActiveSpreadsheet();
let users = ss.getEditors();
let date = new Date();
let sh = e.source.getActiveSheet();
let shName = sh.getSheetName();
let cell = e.range;
let cellData = cell.getValue();
let col = cell.getColumn();
let row = cell.getRow();
let emailCell = sh.getRange(row, 4).getValue();
let dateCell = sh.getRange(4, 5).getValue();

if( shName === 'Charter Dashboard' && col == 1 && row >= 2 && ( cellData === 'Approved'  || cellData === 'Declined') && (emailCell == "" || dateCell == "") ){

  for(i=0; i<users.length; i++){
    if(Session.getActiveUser().getEmail() == users[i].getEmail() ){

      sh.getRange(row, 4).setValue(users[i].getEmail() );
      sh.getRange(row, 5).setValue(date);
    }
  }
}
}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2d943656-7b35-44e8-b1e7-88dbc7c9d83fn%40googlegroups.com.

[Nathan Mahys]

Thank you so much! It gave me this error code when I tried....

9:05:23 AM

Error

TypeError: Cannot read properties of undefined (reading 'source')

onEdit

@ Code.gs:5

[Keith Andersen]

Here's my test sheet. It's view only. Make a copy so that you become owner with editor's rights and then try it yourself. You will need to give permissions for the script to run.

https://docs.google.com/spreadsheets/d/1igDTxAX3_FUh58_EK76GjYeniZQLwnft9e8LGV0l3ro/edit?usp=drivesdk

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b6210d73-adba-4e93-b2ff-5f2f964e5b97n%40googlegroups.com.

[Keith Andersen]

When you get that error. Are you getting it by running it in the script editor or by changing the value in the column?

I'm kind of at a loss because I wrote that on a sheet and tested it several times and it worked on my end.

On Tue, Jun 11, 2024, 10:06 AM Nathan Mahys <nat...@geitire.com> wrote:

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b6210d73-adba-4e93-b2ff-5f2f964e5b97n%40googlegroups.com.

[Nathan Mahys]

I was getting the error in the script editor but ran it again and now it is working!!!! Thank you! Very much! This is awesome 

[Keith Andersen]

Yes, on edit will always give an error in the script editor because it needs an actual edit on the sheet in order to run.

You're very welcome.
Cheers

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8342eaeb-07bd-4b76-bf2a-68b335430190n%40googlegroups.com.