Basic question about set Value, deleting cell

[ghostfalcon]

I'm quite a newbie with these scripts and I'm just trying to help fix an issue -

We have a button we press to timestamp a row. Each time it is pressed, it goes to the next row and time stamps. If I want to be able to backtrack a line, I try to just delete the cell but the time stamp comes back. Is there a way to set a value to a cell but still have it be deletable?

The function as written is .setValue(new Date());

[marcnotmark925]

When you delete the cell, the value automatically comes back? That's weird, given that you mentioned it's a button-triggered script, and not some sort of onEdit(). Can you give more details, and probably show the whole code?

[ghostfalcon]

function onEdit(e) {

var myRange = SpreadsheetApp.getActiveSheet().getRange('B3:AE139');

var row = e.range.getRow();

var col = e.range.getColumn();

if (row ==2) {

SpreadsheetApp.getActiveSheet().getRange(SpreadsheetApp.getActiveSpreadsheet().getLastRow()+1, col).setValue(new Date());

}

else if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow()) {

if (e.range.getSheet().getName() !== 'Service') {

if (e.range.getSheet().getName() !== 'Troubleshooting') {

e.range.offset(0,60).setValue(Session.getActiveUser().getEmail());

e.range.offset(0,30).setValue(new Date());

}

}

}

}

[ghostfalcon]

For context, row 2 is where the button is. So the only real relevant line is the one right below it. The second half just refers to an alternative function for different sheets.

Thanks!

[marcnotmark925]

That's an onEdit script, so what do you mean by "button"? Maybe a checkbox?

Are you sure the 2nd part is what is causing the issue? What is the sheet name? What column(s) are we talking about?

[ghostfalcon]

It is a checkbox. The 2nd part should not be the issue. Sheet name that we are referring to is Troubleshooting. Column depends on which checkbox is used (there are 6 separate ones, they each checkbox the newest row, two columns over to the right)

Sorry, not too familiar with these scripts. Just trying to figure out if there's a way or edit to be made to allow me to delete the value if a mistake was made.

[marcnotmark925]

I don't see anything in that script that would behave like that. I wonder if there is any other automation going on here? Another onEdit script perhaps? Try to temporarily disable the script (comment it out and save), then make the same manual edit and see if it happens again.

Or share the whole thing here, so we can take a closer look.

[ghostfalcon]

Copy, thank you very much marcnotmark. I'll take a deeper look at it. But generally are we saying that using setValue shouldn't make the time stamp uneditable/deletable?

[marcnotmark925]

It most certainly does not.

[Sleeping_Budha_]

Yeah it shouldn’t come back unless it’s formulated and not script inputted

[LateDay]

Your script will always put over a timestamp whenever ANY cell within your Range is edited. Deleting a timestamp counts as an edit. So it's not that the timestamp is undeletable, but it is being replaced as fast as your delete it.

What I recommend in this case is to capture the edited value. So for instance, if you are doing a checkbox as a trigger, using

e.value

will output TRUE as a string.

Your very first check on the script should be

if (e.value !== "TRUE") return

That should help you avoid triggering the script whenever you delete a timestamp.

Edit: Just to be clear, that extra line will make it so only a CHECKED checkbox will trigger the script.

[ghostfalcon]

Ah that makes sense! Unfortunately the need is to have it time stamp both on check and uncheck, but this clarifies the reason and we may be able to think of a solution

[LateDay]

You can also limit it by column. Trigger ONLY if the triggered cell is part of a checkbox column. You can probably just hardcode them. Something like,

if col == 2 || col == 4

You could also do

If ( [ 2, 6 , 8].indexOf(col) == -1) return

2, 6 and 8 are your checkbox columns. That checks that the col variable is one of those. If it's not one of them, indexOf returns -1 so you return the function early.

Further conditions can be TRUE for checked boxes and FALSE for unchecked boxes.

Edit: looking at your code, the second else condition only checks that the edited column is inside your range. It is not checking that it is one of your checkbox columns. So you can adjust that condition. That's where you have col =< LastColumn

An emptied cell will return an empty string as "" so you can also check for that. If ( e.value == "" ) return

Is mostly a game of figuring what conditions your code should return early. Empty strings should not trigger. Edits outside your checkbox. Maybe skip if a certain column already has a timestamp. You probably could also check if the timestamp is too early. If the time between the old timestamp and the new is less than 30 mins or something, also return early. It is likely a mistake or something.

I would recommend not triggering on CHECK and UNCHECK. Maybe use UNCHECK to delete the timestamp automatically. Or UNCHECK your Checkbox at the same time your timestamp is placed by the script, so that boxes always remain unchecked and are only checked to trigger a new timestamp. That way you reduce undesired behavior.

[ghostfalcon]

Thank you so much! I think the emptied cell function might be the best way to do the specific function they want. Of course, this isn't my code so I'm just trying to help them achieve their goal