Google Script to execute IF Statement in a loop for date value
I'm working on a script to automate a daily copy+paste (as value) function. I've worked through it piece-by-piece and my final issue is the looped if-statement that looks at the date relative to today's date.
You can see that in column B is my 'Date' column and in row 1 column AN I entered a today() function.
Essentially, everyday I copy and paste (as value) any rows (columns I - AM) that match 'today's' date, highlight them grey and then hide the rows. Once I can set up the if-statement for the loop to recognize the correct rows, I can set a daily trigger in the morning to run the function.
The code is below - any and all help is greatly appreciated!
function dailyUpdate()
{
var sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Results');
var dateValues = sht.getRange(2,2,1231).getValues();
for (r=1; r<1232; r++)
var todayValue = sht.getRange(1,39).getValue();
var dateValues = sht.getRange(r,2).getValues();
if(dateValues == todayValue)
{
var source = sht.getRange(r,9,1,31);
source.copyTo(sht.getRange(r,9,1,31), {contentsOnly: true});
sht.hideRow(source)
source.setBackground("grey");
}
}
for-loop if-statement
google-apps-script google-sheets asked Dec 28 '18 at 5:42
BasketballAutomationBasketballAutomation
31
add a comment |
I'm working on a script to automate a daily copy+paste (as value) function. I've worked through it piece-by-piece and my final issue is the looped if-statement that looks at the date relative to today's date.
You can see that in column B is my 'Date' column and in row 1 column AN I entered a today() function.
Essentially, everyday I copy and paste (as value) any rows (columns I - AM) that match 'today's' date, highlight them grey and then hide the rows. Once I can set up the if-statement for the loop to recognize the correct rows, I can set a daily trigger in the morning to run the function.
The code is below - any and all help is greatly appreciated!
function dailyUpdate()
{
var sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Results');
var dateValues = sht.getRange(2,2,1231).getValues();
for (r=1; r<1232; r++)
var todayValue = sht.getRange(1,39).getValue();
var dateValues = sht.getRange(r,2).getValues();
if(dateValues == todayValue)
{
var source = sht.getRange(r,9,1,31);
source.copyTo(sht.getRange(r,9,1,31), {contentsOnly: true});
sht.hideRow(source)
source.setBackground("grey");
}
}
for-loop if-statement
google-apps-script google-sheets asked Dec 28 '18 at 5:42
BasketballAutomationBasketballAutomation
31
You have the same source and destination in "copyTo"... What is the sense? In the line "var dateValues = sht.getRange(r,2).getValues();" you have a single cell reference. May be you want "getValue" instead of "getValues"?
– Александр Ермолин
Dec 28 '18 at 7:10
Adjusted for the single cell reference, however I'm copying to the same place as the source as it copying-to as "true" or 'values only.' This is to remove the in-place formulas and only have the results.
– BasketballAutomation
Dec 29 '18 at 16:49
add a comment |
I'm working on a script to automate a daily copy+paste (as value) function. I've worked through it piece-by-piece and my final issue is the looped if-statement that looks at the date relative to today's date.
You can see that in column B is my 'Date' column and in row 1 column AN I entered a today() function.
Essentially, everyday I copy and paste (as value) any rows (columns I - AM) that match 'today's' date, highlight them grey and then hide the rows. Once I can set up the if-statement for the loop to recognize the correct rows, I can set a daily trigger in the morning to run the function.
The code is below - any and all help is greatly appreciated!
function dailyUpdate()
{
var sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Results');
var dateValues = sht.getRange(2,2,1231).getValues();
for (r=1; r<1232; r++)
var todayValue = sht.getRange(1,39).getValue();
var dateValues = sht.getRange(r,2).getValues();
if(dateValues == todayValue)
{
var source = sht.getRange(r,9,1,31);
source.copyTo(sht.getRange(r,9,1,31), {contentsOnly: true});
sht.hideRow(source)
source.setBackground("grey");
}
}
for-loop if-statement
google-apps-script google-sheets asked Dec 28 '18 at 5:42
BasketballAutomationBasketballAutomation
31
I'm working on a script to automate a daily copy+paste (as value) function. I've worked through it piece-by-piece and my final issue is the looped if-statement that looks at the date relative to today's date.
You can see that in column B is my 'Date' column and in row 1 column AN I entered a today() function.
Essentially, everyday I copy and paste (as value) any rows (columns I - AM) that match 'today's' date, highlight them grey and then hide the rows. Once I can set up the if-statement for the loop to recognize the correct rows, I can set a daily trigger in the morning to run the function.
The code is below - any and all help is greatly appreciated!
function dailyUpdate()
{
var sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Results');
var dateValues = sht.getRange(2,2,1231).getValues();
for (r=1; r<1232; r++)
var todayValue = sht.getRange(1,39).getValue();
var dateValues = sht.getRange(r,2).getValues();
if(dateValues == todayValue)
{
var source = sht.getRange(r,9,1,31);
source.copyTo(sht.getRange(r,9,1,31), {contentsOnly: true});
sht.hideRow(source)
source.setBackground("grey");
}
}
for-loop if-statement
google-apps-script google-sheets for-loop if-statement
google-apps-script google-sheets asked Dec 28 '18 at 5:42
BasketballAutomationBasketballAutomation
31
asked Dec 28 '18 at 5:42
BasketballAutomationBasketballAutomation
31
asked Dec 28 '18 at 5:42
BasketballAutomationBasketballAutomation
31
asked Dec 28 '18 at 5:42
BasketballAutomationBasketballAutomation
31
asked Dec 28 '18 at 5:42
BasketballAutomationBasketballAutomation
31
31
You have the same source and destination in "copyTo"... What is the sense? In the line "var dateValues = sht.getRange(r,2).getValues();" you have a single cell reference. May be you want "getValue" instead of "getValues"?
– Александр Ермолин
Dec 28 '18 at 7:10
Adjusted for the single cell reference, however I'm copying to the same place as the source as it copying-to as "true" or 'values only.' This is to remove the in-place formulas and only have the results.
– BasketballAutomation
Dec 29 '18 at 16:49
add a comment |
You have the same source and destination in "copyTo"... What is the sense? In the line "var dateValues = sht.getRange(r,2).getValues();" you have a single cell reference. May be you want "getValue" instead of "getValues"?
– Александр Ермолин
Dec 28 '18 at 7:10
Adjusted for the single cell reference, however I'm copying to the same place as the source as it copying-to as "true" or 'values only.' This is to remove the in-place formulas and only have the results.
– BasketballAutomation
Dec 29 '18 at 16:49
You have the same source and destination in "copyTo"... What is the sense? In the line "var dateValues = sht.getRange(r,2).getValues();" you have a single cell reference. May be you want "getValue" instead of "getValues"?
– Александр Ермолин
Dec 28 '18 at 7:10
You have the same source and destination in "copyTo"... What is the sense? In the line "var dateValues = sht.getRange(r,2).getValues();" you have a single cell reference. May be you want "getValue" instead of "getValues"?
– Александр Ермолин
Dec 28 '18 at 7:10
Adjusted for the single cell reference, however I'm copying to the same place as the source as it copying-to as "true" or 'values only.' This is to remove the in-place formulas and only have the results.
– BasketballAutomation
Dec 29 '18 at 16:49
Adjusted for the single cell reference, however I'm copying to the same place as the source as it copying-to as "true" or 'values only.' This is to remove the in-place formulas and only have the results.
– BasketballAutomation
Dec 29 '18 at 16:49
add a comment |
1 Answer
1
active
oldest
votes
You're comparing sht.getRange(1,39).getValue() to sht.getRange(r,2).getValues(). getValue() returns a single value, where as getValues() returns a 2D array.
And since you're using formula in your sheet try getDisplayValue() instead of getValues().
Try something like this:
function dailyUpdate() {
var sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Results');
var todayValue = sht.getRange(1,39).getDisplayValue();
for (r=1; r<1232; r++) {
var dateValue = sht.getRange(r,2).getDisplayValue();
if(dateValue == todayValue) {
var source = sht.getRange(r,9,1,31);
source.copyTo(sht.getRange(r,9,1,31), {contentsOnly: true});
sht.hideRow(source)
source.setBackground("grey");
}
}
}
answered Dec 28 '18 at 9:23
Mohammad UmairMohammad Umair
1,8881720
Great catch - for some reason still not executing properly however. For what it's worth, the function now takes a couple of minutes to run, so I'm assuming that it is cycling through the necessary rows. Any other troubleshooting ideas?
– BasketballAutomation
Dec 29 '18 at 16:45
Never mind - all set now. Thank you!
– BasketballAutomation
Dec 29 '18 at 17:02
Sorry - Now only seeing your text, Great! You made it work! what was off ?
– Mohammad Umair
Dec 29 '18 at 17:45
1
Hey sorry for the delayed response - it was my own formatting! Your answer was spot on.
– BasketballAutomation
2 days ago
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%2f53954145%2fgoogle-script-to-execute-if-statement-in-a-loop-for-date-value%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
You're comparing sht.getRange(1,39).getValue() to sht.getRange(r,2).getValues(). getValue() returns a single value, where as getValues() returns a 2D array.
And since you're using formula in your sheet try getDisplayValue() instead of getValues().
Try something like this:
function dailyUpdate() {
var sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Results');
var todayValue = sht.getRange(1,39).getDisplayValue();
for (r=1; r<1232; r++) {
var dateValue = sht.getRange(r,2).getDisplayValue();
if(dateValue == todayValue) {
var source = sht.getRange(r,9,1,31);
source.copyTo(sht.getRange(r,9,1,31), {contentsOnly: true});
sht.hideRow(source)
source.setBackground("grey");
}
}
}
answered Dec 28 '18 at 9:23
Mohammad UmairMohammad Umair
1,8881720
Great catch - for some reason still not executing properly however. For what it's worth, the function now takes a couple of minutes to run, so I'm assuming that it is cycling through the necessary rows. Any other troubleshooting ideas?
– BasketballAutomation
Dec 29 '18 at 16:45
Never mind - all set now. Thank you!
– BasketballAutomation
Dec 29 '18 at 17:02
Sorry - Now only seeing your text, Great! You made it work! what was off ?
– Mohammad Umair
Dec 29 '18 at 17:45
1
Hey sorry for the delayed response - it was my own formatting! Your answer was spot on.
– BasketballAutomation
2 days ago
add a comment |
You're comparing sht.getRange(1,39).getValue() to sht.getRange(r,2).getValues(). getValue() returns a single value, where as getValues() returns a 2D array.
And since you're using formula in your sheet try getDisplayValue() instead of getValues().
Try something like this:
function dailyUpdate() {
var sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Results');
var todayValue = sht.getRange(1,39).getDisplayValue();
for (r=1; r<1232; r++) {
var dateValue = sht.getRange(r,2).getDisplayValue();
if(dateValue == todayValue) {
var source = sht.getRange(r,9,1,31);
source.copyTo(sht.getRange(r,9,1,31), {contentsOnly: true});
sht.hideRow(source)
source.setBackground("grey");
}
}
}
answered Dec 28 '18 at 9:23
Mohammad UmairMohammad Umair
1,8881720
Great catch - for some reason still not executing properly however. For what it's worth, the function now takes a couple of minutes to run, so I'm assuming that it is cycling through the necessary rows. Any other troubleshooting ideas?
– BasketballAutomation
Dec 29 '18 at 16:45
Never mind - all set now. Thank you!
– BasketballAutomation
Dec 29 '18 at 17:02
Sorry - Now only seeing your text, Great! You made it work! what was off ?
– Mohammad Umair
Dec 29 '18 at 17:45
1
Hey sorry for the delayed response - it was my own formatting! Your answer was spot on.
– BasketballAutomation
2 days ago
add a comment |
You're comparing sht.getRange(1,39).getValue() to sht.getRange(r,2).getValues(). getValue() returns a single value, where as getValues() returns a 2D array.
And since you're using formula in your sheet try getDisplayValue() instead of getValues().
Try something like this:
function dailyUpdate() {
var sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Results');
var todayValue = sht.getRange(1,39).getDisplayValue();
for (r=1; r<1232; r++) {
var dateValue = sht.getRange(r,2).getDisplayValue();
if(dateValue == todayValue) {
var source = sht.getRange(r,9,1,31);
source.copyTo(sht.getRange(r,9,1,31), {contentsOnly: true});
sht.hideRow(source)
source.setBackground("grey");
}
}
}
answered Dec 28 '18 at 9:23
Mohammad UmairMohammad Umair
1,8881720
You're comparing sht.getRange(1,39).getValue() to sht.getRange(r,2).getValues(). getValue() returns a single value, where as getValues() returns a 2D array.
And since you're using formula in your sheet try getDisplayValue() instead of getValues().
Try something like this:
function dailyUpdate() {
var sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Copy of Results');
var todayValue = sht.getRange(1,39).getDisplayValue();
for (r=1; r<1232; r++) {
var dateValue = sht.getRange(r,2).getDisplayValue();
if(dateValue == todayValue) {
var source = sht.getRange(r,9,1,31);
source.copyTo(sht.getRange(r,9,1,31), {contentsOnly: true});
sht.hideRow(source)
source.setBackground("grey");
}
}
}
answered Dec 28 '18 at 9:23
Mohammad UmairMohammad Umair
1,8881720
answered Dec 28 '18 at 9:23
Mohammad UmairMohammad Umair
1,8881720
answered Dec 28 '18 at 9:23
Mohammad UmairMohammad Umair
1,8881720
answered Dec 28 '18 at 9:23
Mohammad UmairMohammad Umair
1,8881720
1,8881720
Great catch - for some reason still not executing properly however. For what it's worth, the function now takes a couple of minutes to run, so I'm assuming that it is cycling through the necessary rows. Any other troubleshooting ideas?
– BasketballAutomation
Dec 29 '18 at 16:45
Never mind - all set now. Thank you!
– BasketballAutomation
Dec 29 '18 at 17:02
Sorry - Now only seeing your text, Great! You made it work! what was off ?
– Mohammad Umair
Dec 29 '18 at 17:45
1
Hey sorry for the delayed response - it was my own formatting! Your answer was spot on.
– BasketballAutomation
2 days ago
add a comment |
Great catch - for some reason still not executing properly however. For what it's worth, the function now takes a couple of minutes to run, so I'm assuming that it is cycling through the necessary rows. Any other troubleshooting ideas?
– BasketballAutomation
Dec 29 '18 at 16:45
Never mind - all set now. Thank you!
– BasketballAutomation
Dec 29 '18 at 17:02
Sorry - Now only seeing your text, Great! You made it work! what was off ?
– Mohammad Umair
Dec 29 '18 at 17:45
1
Hey sorry for the delayed response - it was my own formatting! Your answer was spot on.
– BasketballAutomation
2 days ago
Great catch - for some reason still not executing properly however. For what it's worth, the function now takes a couple of minutes to run, so I'm assuming that it is cycling through the necessary rows. Any other troubleshooting ideas?
– BasketballAutomation
Dec 29 '18 at 16:45
Great catch - for some reason still not executing properly however. For what it's worth, the function now takes a couple of minutes to run, so I'm assuming that it is cycling through the necessary rows. Any other troubleshooting ideas?
– BasketballAutomation
Dec 29 '18 at 16:45
Never mind - all set now. Thank you!
– BasketballAutomation
Dec 29 '18 at 17:02
Never mind - all set now. Thank you!
– BasketballAutomation
Dec 29 '18 at 17:02
Sorry - Now only seeing your text, Great! You made it work! what was off ?
– Mohammad Umair
Dec 29 '18 at 17:45
Sorry - Now only seeing your text, Great! You made it work! what was off ?
– Mohammad Umair
Dec 29 '18 at 17:45
1
1
Hey sorry for the delayed response - it was my own formatting! Your answer was spot on.
– BasketballAutomation
2 days ago
Hey sorry for the delayed response - it was my own formatting! Your answer was spot on.
– BasketballAutomation
2 days ago
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%2f53954145%2fgoogle-script-to-execute-if-statement-in-a-loop-for-date-value%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
You have the same source and destination in "copyTo"... What is the sense? In the line "var dateValues = sht.getRange(r,2).getValues();" you have a single cell reference. May be you want "getValue" instead of "getValues"?
– Александр Ермолин
Dec 28 '18 at 7:10
Adjusted for the single cell reference, however I'm copying to the same place as the source as it copying-to as "true" or 'values only.' This is to remove the in-place formulas and only have the results.
– BasketballAutomation
Dec 29 '18 at 16:49