Trouble with a timestamp
I have been having trouble with getting a timestamp to work with a certain set of conditions.
I need it to give me a timestamp on two different columns once the conditions are met. The first timestamp should appear when the second column gets fill out (this part is actually working).
The second timestamp should appear on the 7 column only when the word "COMPLETE" is selected out of a drop-down list, something that's not happening since no matter whats chosen out of the list the timestamp appears an I'm not sure where the issue is at.
This is the code I have been using for this:
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Notes" ) { //checks that we're on the correct sheet
var r = s.getActiveCell();
if( r.getColumn() == 2 ) { //checks the column
var nextCell = r.offset(0, 1);
//if( nextCell.getValue() !== '' ) //is empty?
nextCell.setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy"));
}
if( r.getColumn() == 6 ) { //checks the column
var nextCell = r.offset(0, 1);
//if( nextCell.getValue() !== 'COMPLETE')
nextCell.setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy HH:mm:ss"));
}
}
}
If anyone could help me with this please let me know.
google-apps-script google-sheets triggers timestamp edited Nov 24 '18 at 0:38
Rubén
10.3k43466
asked Nov 23 '18 at 18:01
Andrés Arévalo
61
add a comment |
I have been having trouble with getting a timestamp to work with a certain set of conditions.
I need it to give me a timestamp on two different columns once the conditions are met. The first timestamp should appear when the second column gets fill out (this part is actually working).
The second timestamp should appear on the 7 column only when the word "COMPLETE" is selected out of a drop-down list, something that's not happening since no matter whats chosen out of the list the timestamp appears an I'm not sure where the issue is at.
This is the code I have been using for this:
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Notes" ) { //checks that we're on the correct sheet
var r = s.getActiveCell();
if( r.getColumn() == 2 ) { //checks the column
var nextCell = r.offset(0, 1);
//if( nextCell.getValue() !== '' ) //is empty?
nextCell.setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy"));
}
if( r.getColumn() == 6 ) { //checks the column
var nextCell = r.offset(0, 1);
//if( nextCell.getValue() !== 'COMPLETE')
nextCell.setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy HH:mm:ss"));
}
}
}
If anyone could help me with this please let me know.
google-apps-script google-sheets triggers timestamp edited Nov 24 '18 at 0:38
Rubén
10.3k43466
asked Nov 23 '18 at 18:01
Andrés Arévalo
61
Add a screenshot of your spreadsheet in order to be able to check that your code is using the right sheet name and column values.. Beside that consider to add some console.log in order to help you to debug your code.
– Rubén
Nov 24 '18 at 0:52
add a comment |
I have been having trouble with getting a timestamp to work with a certain set of conditions.
I need it to give me a timestamp on two different columns once the conditions are met. The first timestamp should appear when the second column gets fill out (this part is actually working).
The second timestamp should appear on the 7 column only when the word "COMPLETE" is selected out of a drop-down list, something that's not happening since no matter whats chosen out of the list the timestamp appears an I'm not sure where the issue is at.
This is the code I have been using for this:
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Notes" ) { //checks that we're on the correct sheet
var r = s.getActiveCell();
if( r.getColumn() == 2 ) { //checks the column
var nextCell = r.offset(0, 1);
//if( nextCell.getValue() !== '' ) //is empty?
nextCell.setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy"));
}
if( r.getColumn() == 6 ) { //checks the column
var nextCell = r.offset(0, 1);
//if( nextCell.getValue() !== 'COMPLETE')
nextCell.setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy HH:mm:ss"));
}
}
}
If anyone could help me with this please let me know.
google-apps-script google-sheets triggers timestamp edited Nov 24 '18 at 0:38
Rubén
10.3k43466
asked Nov 23 '18 at 18:01
Andrés Arévalo
61
I have been having trouble with getting a timestamp to work with a certain set of conditions.
I need it to give me a timestamp on two different columns once the conditions are met. The first timestamp should appear when the second column gets fill out (this part is actually working).
The second timestamp should appear on the 7 column only when the word "COMPLETE" is selected out of a drop-down list, something that's not happening since no matter whats chosen out of the list the timestamp appears an I'm not sure where the issue is at.
This is the code I have been using for this:
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Notes" ) { //checks that we're on the correct sheet
var r = s.getActiveCell();
if( r.getColumn() == 2 ) { //checks the column
var nextCell = r.offset(0, 1);
//if( nextCell.getValue() !== '' ) //is empty?
nextCell.setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy"));
}
if( r.getColumn() == 6 ) { //checks the column
var nextCell = r.offset(0, 1);
//if( nextCell.getValue() !== 'COMPLETE')
nextCell.setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy HH:mm:ss"));
}
}
}
If anyone could help me with this please let me know.
google-apps-script google-sheets triggers timestamp google-apps-script google-sheets triggers timestamp edited Nov 24 '18 at 0:38
Rubén
10.3k43466
asked Nov 23 '18 at 18:01
Andrés Arévalo
61
edited Nov 24 '18 at 0:38
Rubén
10.3k43466
asked Nov 23 '18 at 18:01
Andrés Arévalo
61
edited Nov 24 '18 at 0:38
Rubén
10.3k43466
edited Nov 24 '18 at 0:38
Rubén
10.3k43466
edited Nov 24 '18 at 0:38
Rubén
10.3k43466
10.3k43466
asked Nov 23 '18 at 18:01
Andrés Arévalo
61
asked Nov 23 '18 at 18:01
Andrés Arévalo
61
asked Nov 23 '18 at 18:01
Andrés Arévalo
61
61
Add a screenshot of your spreadsheet in order to be able to check that your code is using the right sheet name and column values.. Beside that consider to add some console.log in order to help you to debug your code.
– Rubén
Nov 24 '18 at 0:52
add a comment |
Add a screenshot of your spreadsheet in order to be able to check that your code is using the right sheet name and column values.. Beside that consider to add some console.log in order to help you to debug your code.
– Rubén
Nov 24 '18 at 0:52
Add a screenshot of your spreadsheet in order to be able to check that your code is using the right sheet name and column values.. Beside that consider to add some console.log in order to help you to debug your code.
– Rubén
Nov 24 '18 at 0:52
Add a screenshot of your spreadsheet in order to be able to check that your code is using the right sheet name and column values.. Beside that consider to add some console.log in order to help you to debug your code.
– Rubén
Nov 24 '18 at 0:52
add a comment |
1 Answer
1
active
oldest
votes
Try something like this
function onEdit(e) {
var s, cols, offset, ind;
s = e.source.getActiveSheet();
cols = [2, 6];
offset = [1, 2];
ind = cols.indexOf(e.range.columnStart);
if (s.getName() == "Notes" && (ind == 0 || ind == 1 && e.range.offset(0, 1).getValue() == 'COMPLETE')) {
e.range.offset(0, offset[ind]).setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy HH:mm:ss"));
}
}
Note that this script runs on a simple on Edit trigger. Do not try to run if from the script editor. Instead, try editing the designated columns and see if the timestamp appears.
answered Nov 25 '18 at 13:48
JPV
10.7k21525
add a comment |
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
});
}
});
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%2f53451185%2ftrouble-with-a-timestamp%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
Try something like this
function onEdit(e) {
var s, cols, offset, ind;
s = e.source.getActiveSheet();
cols = [2, 6];
offset = [1, 2];
ind = cols.indexOf(e.range.columnStart);
if (s.getName() == "Notes" && (ind == 0 || ind == 1 && e.range.offset(0, 1).getValue() == 'COMPLETE')) {
e.range.offset(0, offset[ind]).setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy HH:mm:ss"));
}
}
Note that this script runs on a simple on Edit trigger. Do not try to run if from the script editor. Instead, try editing the designated columns and see if the timestamp appears.
answered Nov 25 '18 at 13:48
JPV
10.7k21525
add a comment |
Try something like this
function onEdit(e) {
var s, cols, offset, ind;
s = e.source.getActiveSheet();
cols = [2, 6];
offset = [1, 2];
ind = cols.indexOf(e.range.columnStart);
if (s.getName() == "Notes" && (ind == 0 || ind == 1 && e.range.offset(0, 1).getValue() == 'COMPLETE')) {
e.range.offset(0, offset[ind]).setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy HH:mm:ss"));
}
}
Note that this script runs on a simple on Edit trigger. Do not try to run if from the script editor. Instead, try editing the designated columns and see if the timestamp appears.
answered Nov 25 '18 at 13:48
JPV
10.7k21525
add a comment |
Try something like this
function onEdit(e) {
var s, cols, offset, ind;
s = e.source.getActiveSheet();
cols = [2, 6];
offset = [1, 2];
ind = cols.indexOf(e.range.columnStart);
if (s.getName() == "Notes" && (ind == 0 || ind == 1 && e.range.offset(0, 1).getValue() == 'COMPLETE')) {
e.range.offset(0, offset[ind]).setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy HH:mm:ss"));
}
}
Note that this script runs on a simple on Edit trigger. Do not try to run if from the script editor. Instead, try editing the designated columns and see if the timestamp appears.
answered Nov 25 '18 at 13:48
JPV
10.7k21525
Try something like this
function onEdit(e) {
var s, cols, offset, ind;
s = e.source.getActiveSheet();
cols = [2, 6];
offset = [1, 2];
ind = cols.indexOf(e.range.columnStart);
if (s.getName() == "Notes" && (ind == 0 || ind == 1 && e.range.offset(0, 1).getValue() == 'COMPLETE')) {
e.range.offset(0, offset[ind]).setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyy HH:mm:ss"));
}
}
Note that this script runs on a simple on Edit trigger. Do not try to run if from the script editor. Instead, try editing the designated columns and see if the timestamp appears.
answered Nov 25 '18 at 13:48
JPV
10.7k21525
answered Nov 25 '18 at 13:48
JPV
10.7k21525
answered Nov 25 '18 at 13:48
JPV
10.7k21525
answered Nov 25 '18 at 13:48
JPV
10.7k21525
10.7k21525
add a comment |
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2f53451185%2ftrouble-with-a-timestamp%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
Add a screenshot of your spreadsheet in order to be able to check that your code is using the right sheet name and column values.. Beside that consider to add some console.log in order to help you to debug your code.
– Rubén
Nov 24 '18 at 0:52