Google Sheets Script - How to keep a record when a specific cell value changes

[Adam Kruger]

Hi there!

I'm new to this community and don't have experience yet with Apps Scripts. I'm looking for helping setting up a script that I hope should be pretty simple in Google Sheets. 

I'm trying to set up a script that is triggered whenever a specific cell value is changed (cell A1 for instance). If cell A1's value is changed to "Sarah", I want it to record "Sarah" in cell B1. If cell A1's value is then changed from "Sarah" to "Adam", I want the script to record "Adam" in cell B2. If cell A1's value is then changed from "Adam" to "John", I want the script to record "John" in cell B3. Essentially I want it to keep a record/list in column B of every value change to cell A1. Is that doable? 

Thanks so much for your help! I really appreciate it.

Adam

[Fernando Falcao]

Hi Adam,

I would do something like this:

You can copy the function in your sheet code.

After that, you associate this funcion to the trigger onEdit.

See if it works for you.

Regards,

Fernando

function ChangeLog() {

 let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

 let mainCell = sheet.getActiveCell();

 let mainCellRow = mainCell.getRow();

 let mainCellCol = mainCell.getColumn();

 let mainCellValue = mainCell.getValue();

 let lastRow = sheet.getLastRow();

 if (mainCellRow == 2 && mainCellCol == 1)

 {

    sheet.getRange(lastRow+1,2).setValue(mainCellValue)

    

 }

 

  

}

--
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/59c8fdac-04a3-4e26-9b64-361c67845ccbn%40googlegroups.com.

[CBMServices Web]

When looking at cell changes, a good facility to use is the event parameter that is passed to your trigger function.

It provides you what cell was changed, the old value of the cell and the new value of the cell. You get all this without event having to poke at the spreadsheet.

Check that out.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAD0JAkHzhf_SvGUHvxo%3D81PdpnroGty_0vtgqg3SoUHgYbfOdw%40mail.gmail.com.

[Adam Kruger]

Thank you so much Fernando, this worked! I really appreciate your help!

[Roberto Paixão]

I like to use notes. Try this code,  

function onEdit(e) 

  {

    let changedCell= e.range;   

    let alterDate = Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"dd/MM/yy HH:mm");

              // see in https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

    changedCell.setNote ( alterDate + ", changed to: " + changedCell.getValues() + "\n"+ changedCell.getNote() ); 

  } //  end of onEdit(e)

[Fernando Falcao]

That's true! I didn't know that! :-)

I've rewrote the code in order to use the suggestion you gave!

I will do the same in other uses of onEdit!

Cheers,

Fernando

function onEdit(e) {

let range = e.range;

let oldCellValue = e.oldValue;

let mainCellRow = range.getRow();

let mainCellCol = range.getColumn();

let mainCellValue = range.getValue();

let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

//The command below is to force the focus to the cell where you are typing the value.

//everytime you finish typing the cursor return to the cell A2.

range.activate()

let lastRow = sheet.getLastRow()

//The lines below is to assure that your log will be saved in the right order.

//I included a column to save the date of each edition.

if (mainCellRow == 2 && mainCellCol == 1 && mainCellValue != "")

{

if ( sheet.getRange(lastRow,2).getValue() === "")

{

sheet.getRange(lastRow,2).setValue(oldCellValue)

sheet.getRange(lastRow,3).setValue(new Date())

}

else

{

sheet.getRange(lastRow+1,2).setValue(oldCellValue)

sheet.getRange(lastRow+1,3).setValue(new Date())

}

}

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3Vr3hOgE%3DyG-n43LJApUcxiX9_4wbNWRY9ua81kF74rdQ%40mail.gmail.com.

[Марина Романюк]

Thanks for sharing the code, it's very useful!! Is it possible to adapt it a bit in order to make it see edits on one tab and copy this value to another tab within the sheet?

[Mac Bueno]

Hi,

Can't seem to make this code work, the only thing that's working is the cursor returns to the cell you edit but it doesn't seem to record the edit I made.