How to modify current insert timestamp script to remove timestamp when cell is cleared
0
I am currently using the following script to add a timestamp to a cell in Column H when cell in Column A is populated. Problem is, when I delete the contents of Column A the timestamp remains in Column H. I currently have conditional formatting to hide the timestamp if the cell is empty but I really need the contents to be deleted. How do I modify my current script to accomplish this?
function onEdit(event) {
var sheet = SpreadsheetApp.getActiveSheet();
if(sheet.getName() == "CHECK OUT" || sheet.getName() == "CHECK IN" || sheet.getName() == "MISC OUT" || sheet.getName() == "MISC IN"){
var eventRange = event.range;
if (eventRange.getColumn() == 1) { // 1 == column A
var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 8, eventRange.getNumRows(), 8);
var values = columnARange.getValues();
for (var i = 0; i < values.length; i++) {
values[i][0] = new Date();
}
columnARange.setValues(values);
}
}}
google-apps-script google-sheets timestamp
edited Jan 20 at 0:50
Rubén
10.7k43567
asked Jan 18 at 17:43
Vickie Vanriper - OITVickie Vanriper - OIT
11
New contributor
Vickie Vanriper - OIT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
I am currently using the following script to add a timestamp to a cell in Column H when cell in Column A is populated. Problem is, when I delete the contents of Column A the timestamp remains in Column H. I currently have conditional formatting to hide the timestamp if the cell is empty but I really need the contents to be deleted. How do I modify my current script to accomplish this?
function onEdit(event) {
var sheet = SpreadsheetApp.getActiveSheet();
if(sheet.getName() == "CHECK OUT" || sheet.getName() == "CHECK IN" || sheet.getName() == "MISC OUT" || sheet.getName() == "MISC IN"){
var eventRange = event.range;
if (eventRange.getColumn() == 1) { // 1 == column A
var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 8, eventRange.getNumRows(), 8);
var values = columnARange.getValues();
for (var i = 0; i < values.length; i++) {
values[i][0] = new Date();
}
columnARange.setValues(values);
}
}}
google-apps-script google-sheets timestamp
edited Jan 20 at 0:50
Rubén
10.7k43567
asked Jan 18 at 17:43
Vickie Vanriper - OITVickie Vanriper - OIT
11
New contributor
Vickie Vanriper - OIT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
0
0
I am currently using the following script to add a timestamp to a cell in Column H when cell in Column A is populated. Problem is, when I delete the contents of Column A the timestamp remains in Column H. I currently have conditional formatting to hide the timestamp if the cell is empty but I really need the contents to be deleted. How do I modify my current script to accomplish this?
function onEdit(event) {
var sheet = SpreadsheetApp.getActiveSheet();
if(sheet.getName() == "CHECK OUT" || sheet.getName() == "CHECK IN" || sheet.getName() == "MISC OUT" || sheet.getName() == "MISC IN"){
var eventRange = event.range;
if (eventRange.getColumn() == 1) { // 1 == column A
var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 8, eventRange.getNumRows(), 8);
var values = columnARange.getValues();
for (var i = 0; i < values.length; i++) {
values[i][0] = new Date();
}
columnARange.setValues(values);
}
}}
google-apps-script google-sheets timestamp
edited Jan 20 at 0:50
Rubén
10.7k43567
asked Jan 18 at 17:43
Vickie Vanriper - OITVickie Vanriper - OIT
11
New contributor
Vickie Vanriper - OIT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I am currently using the following script to add a timestamp to a cell in Column H when cell in Column A is populated. Problem is, when I delete the contents of Column A the timestamp remains in Column H. I currently have conditional formatting to hide the timestamp if the cell is empty but I really need the contents to be deleted. How do I modify my current script to accomplish this?
function onEdit(event) {
var sheet = SpreadsheetApp.getActiveSheet();
if(sheet.getName() == "CHECK OUT" || sheet.getName() == "CHECK IN" || sheet.getName() == "MISC OUT" || sheet.getName() == "MISC IN"){
var eventRange = event.range;
if (eventRange.getColumn() == 1) { // 1 == column A
var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 8, eventRange.getNumRows(), 8);
var values = columnARange.getValues();
for (var i = 0; i < values.length; i++) {
values[i][0] = new Date();
}
columnARange.setValues(values);
}
}}
google-apps-script google-sheets timestamp
google-apps-script google-sheets timestamp
edited Jan 20 at 0:50
Rubén
10.7k43567
asked Jan 18 at 17:43
Vickie Vanriper - OITVickie Vanriper - OIT
11
New contributor
Vickie Vanriper - OIT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Jan 20 at 0:50
Rubén
10.7k43567
asked Jan 18 at 17:43
Vickie Vanriper - OITVickie Vanriper - OIT
11
New contributor
Vickie Vanriper - OIT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Jan 20 at 0:50
Rubén
10.7k43567
edited Jan 20 at 0:50
Rubén
10.7k43567
edited Jan 20 at 0:50
Rubén
10.7k43567
10.7k43567
asked Jan 18 at 17:43
Vickie Vanriper - OITVickie Vanriper - OIT
11
New contributor
Vickie Vanriper - OIT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jan 18 at 17:43
Vickie Vanriper - OITVickie Vanriper - OIT
11
asked Jan 18 at 17:43
Vickie Vanriper - OITVickie Vanriper - OIT
11
11
New contributor
Vickie Vanriper - OIT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Vickie Vanriper - OIT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Vickie Vanriper - OIT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
Try this:
function onEdit(e) {
var rg=e.range;
var sh=e.range.getSheet();
var name=sh.getName();
if(name=="CHECK OUT" || name== "CHECK IN" || name== "MISC OUT" || name=="MISC IN"){
if(rg.columnStart==1) {
var vA=rg.getValues();
for(var i=0;i<vA.length;i++){
if(vA[i][0]) {
sh.getRange(rg.rowStart + i,8).setValue(new Date());
}else{
sh.getRange(rg.rowStart + i,8).setValue('');
}
}
}
}
}
answered Jan 18 at 21:24
CooperCooper
6,7912726
You are amazing! That worked perfectly! Thank you very much!!!!
– Vickie Vanriper - OIT
Jan 18 at 22:16
Please consider marking @Cooper's answer as accepted. :)
– s1c0j1
Jan 18 at 22:23
Make sure that you get the last edit. It turns that thee.valueinif(e.value && e.value.length>0)is really important because e.value is not defined when you delete cell contents. It took me a while to figure that out. It's really helpful to have the new executions page.
– Cooper
Jan 18 at 22:29
I'm not quite sure what you mean in your last comment. Also, I ran in to a problem with this. If you delete the contents in column A cell by cell it works but if you highlight several cells in Column A and select delete it does NOT work. Any suggestions?
– Vickie Vanriper - OIT
yesterday
When I debug it is giving me the following error: TypeError: Cannot read property "range" from undefined. (line 2, file "TimeStamp". This section is highlighted: var rg=e.range;
– Vickie Vanriper - OIT
yesterday
|
show 4 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Vickie Vanriper - OIT is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54259009%2fhow-to-modify-current-insert-timestamp-script-to-remove-timestamp-when-cell-is-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Try this:
function onEdit(e) {
var rg=e.range;
var sh=e.range.getSheet();
var name=sh.getName();
if(name=="CHECK OUT" || name== "CHECK IN" || name== "MISC OUT" || name=="MISC IN"){
if(rg.columnStart==1) {
var vA=rg.getValues();
for(var i=0;i<vA.length;i++){
if(vA[i][0]) {
sh.getRange(rg.rowStart + i,8).setValue(new Date());
}else{
sh.getRange(rg.rowStart + i,8).setValue('');
}
}
}
}
}
answered Jan 18 at 21:24
CooperCooper
6,7912726
You are amazing! That worked perfectly! Thank you very much!!!!
– Vickie Vanriper - OIT
Jan 18 at 22:16
Please consider marking @Cooper's answer as accepted. :)
– s1c0j1
Jan 18 at 22:23
Make sure that you get the last edit. It turns that thee.valueinif(e.value && e.value.length>0)is really important because e.value is not defined when you delete cell contents. It took me a while to figure that out. It's really helpful to have the new executions page.
– Cooper
Jan 18 at 22:29
I'm not quite sure what you mean in your last comment. Also, I ran in to a problem with this. If you delete the contents in column A cell by cell it works but if you highlight several cells in Column A and select delete it does NOT work. Any suggestions?
– Vickie Vanriper - OIT
yesterday
When I debug it is giving me the following error: TypeError: Cannot read property "range" from undefined. (line 2, file "TimeStamp". This section is highlighted: var rg=e.range;
– Vickie Vanriper - OIT
yesterday
|
show 4 more comments
1
Try this:
function onEdit(e) {
var rg=e.range;
var sh=e.range.getSheet();
var name=sh.getName();
if(name=="CHECK OUT" || name== "CHECK IN" || name== "MISC OUT" || name=="MISC IN"){
if(rg.columnStart==1) {
var vA=rg.getValues();
for(var i=0;i<vA.length;i++){
if(vA[i][0]) {
sh.getRange(rg.rowStart + i,8).setValue(new Date());
}else{
sh.getRange(rg.rowStart + i,8).setValue('');
}
}
}
}
}
answered Jan 18 at 21:24
CooperCooper
6,7912726
You are amazing! That worked perfectly! Thank you very much!!!!
– Vickie Vanriper - OIT
Jan 18 at 22:16
Please consider marking @Cooper's answer as accepted. :)
– s1c0j1
Jan 18 at 22:23
Make sure that you get the last edit. It turns that thee.valueinif(e.value && e.value.length>0)is really important because e.value is not defined when you delete cell contents. It took me a while to figure that out. It's really helpful to have the new executions page.
– Cooper
Jan 18 at 22:29
I'm not quite sure what you mean in your last comment. Also, I ran in to a problem with this. If you delete the contents in column A cell by cell it works but if you highlight several cells in Column A and select delete it does NOT work. Any suggestions?
– Vickie Vanriper - OIT
yesterday
When I debug it is giving me the following error: TypeError: Cannot read property "range" from undefined. (line 2, file "TimeStamp". This section is highlighted: var rg=e.range;
– Vickie Vanriper - OIT
yesterday
|
show 4 more comments
1
1
1
Try this:
function onEdit(e) {
var rg=e.range;
var sh=e.range.getSheet();
var name=sh.getName();
if(name=="CHECK OUT" || name== "CHECK IN" || name== "MISC OUT" || name=="MISC IN"){
if(rg.columnStart==1) {
var vA=rg.getValues();
for(var i=0;i<vA.length;i++){
if(vA[i][0]) {
sh.getRange(rg.rowStart + i,8).setValue(new Date());
}else{
sh.getRange(rg.rowStart + i,8).setValue('');
}
}
}
}
}
answered Jan 18 at 21:24
CooperCooper
6,7912726
Try this:
function onEdit(e) {
var rg=e.range;
var sh=e.range.getSheet();
var name=sh.getName();
if(name=="CHECK OUT" || name== "CHECK IN" || name== "MISC OUT" || name=="MISC IN"){
if(rg.columnStart==1) {
var vA=rg.getValues();
for(var i=0;i<vA.length;i++){
if(vA[i][0]) {
sh.getRange(rg.rowStart + i,8).setValue(new Date());
}else{
sh.getRange(rg.rowStart + i,8).setValue('');
}
}
}
}
}
answered Jan 18 at 21:24
CooperCooper
6,7912726
edited yesterday
answered Jan 18 at 21:24
CooperCooper
6,7912726
answered Jan 18 at 21:24
CooperCooper
6,7912726
answered Jan 18 at 21:24
CooperCooper
6,7912726
6,7912726
You are amazing! That worked perfectly! Thank you very much!!!!
– Vickie Vanriper - OIT
Jan 18 at 22:16
Please consider marking @Cooper's answer as accepted. :)
– s1c0j1
Jan 18 at 22:23
Make sure that you get the last edit. It turns that thee.valueinif(e.value && e.value.length>0)is really important because e.value is not defined when you delete cell contents. It took me a while to figure that out. It's really helpful to have the new executions page.
– Cooper
Jan 18 at 22:29
I'm not quite sure what you mean in your last comment. Also, I ran in to a problem with this. If you delete the contents in column A cell by cell it works but if you highlight several cells in Column A and select delete it does NOT work. Any suggestions?
– Vickie Vanriper - OIT
yesterday
When I debug it is giving me the following error: TypeError: Cannot read property "range" from undefined. (line 2, file "TimeStamp". This section is highlighted: var rg=e.range;
– Vickie Vanriper - OIT
yesterday
|
show 4 more comments
You are amazing! That worked perfectly! Thank you very much!!!!
– Vickie Vanriper - OIT
Jan 18 at 22:16
Please consider marking @Cooper's answer as accepted. :)
– s1c0j1
Jan 18 at 22:23
Make sure that you get the last edit. It turns that thee.valueinif(e.value && e.value.length>0)is really important because e.value is not defined when you delete cell contents. It took me a while to figure that out. It's really helpful to have the new executions page.
– Cooper
Jan 18 at 22:29
I'm not quite sure what you mean in your last comment. Also, I ran in to a problem with this. If you delete the contents in column A cell by cell it works but if you highlight several cells in Column A and select delete it does NOT work. Any suggestions?
– Vickie Vanriper - OIT
yesterday
When I debug it is giving me the following error: TypeError: Cannot read property "range" from undefined. (line 2, file "TimeStamp". This section is highlighted: var rg=e.range;
– Vickie Vanriper - OIT
yesterday
You are amazing! That worked perfectly! Thank you very much!!!!
– Vickie Vanriper - OIT
Jan 18 at 22:16
You are amazing! That worked perfectly! Thank you very much!!!!
– Vickie Vanriper - OIT
Jan 18 at 22:16
Please consider marking @Cooper's answer as accepted. :)
– s1c0j1
Jan 18 at 22:23
Please consider marking @Cooper's answer as accepted. :)
– s1c0j1
Jan 18 at 22:23
Make sure that you get the last edit. It turns that the
e.value in if(e.value && e.value.length>0) is really important because e.value is not defined when you delete cell contents. It took me a while to figure that out. It's really helpful to have the new executions page.– Cooper
Jan 18 at 22:29
Make sure that you get the last edit. It turns that the
e.value in if(e.value && e.value.length>0) is really important because e.value is not defined when you delete cell contents. It took me a while to figure that out. It's really helpful to have the new executions page.– Cooper
Jan 18 at 22:29
I'm not quite sure what you mean in your last comment. Also, I ran in to a problem with this. If you delete the contents in column A cell by cell it works but if you highlight several cells in Column A and select delete it does NOT work. Any suggestions?
– Vickie Vanriper - OIT
yesterday
I'm not quite sure what you mean in your last comment. Also, I ran in to a problem with this. If you delete the contents in column A cell by cell it works but if you highlight several cells in Column A and select delete it does NOT work. Any suggestions?
– Vickie Vanriper - OIT
yesterday
When I debug it is giving me the following error: TypeError: Cannot read property "range" from undefined. (line 2, file "TimeStamp". This section is highlighted: var rg=e.range;
– Vickie Vanriper - OIT
yesterday
When I debug it is giving me the following error: TypeError: Cannot read property "range" from undefined. (line 2, file "TimeStamp". This section is highlighted: var rg=e.range;
– Vickie Vanriper - OIT
yesterday
|
show 4 more comments
Vickie Vanriper - OIT is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Vickie Vanriper - OIT is a new contributor. Be nice, and check out our Code of Conduct.
Vickie Vanriper - OIT is a new contributor. Be nice, and check out our Code of Conduct.
Vickie Vanriper - OIT is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54259009%2fhow-to-modify-current-insert-timestamp-script-to-remove-timestamp-when-cell-is-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
