Mixing
If statement with time equals 0:00 Google Apps Script [duplicate]
-1
This question already has an answer here:
Compare two dates with JavaScript
35 answers
I have issues with a cell in google apps script where if a cell = "0:00" it needs to equal some text like "Unexcused Absence". when I run the code it gives me a blank cell.
function Boh() {
var sheet = SpreadsheetApp;
var ss = sheet.getActiveSpreadsheet().getActiveSheet();
var lastRow = ss.getLastRow();
var spreadsheet = SpreadsheetApp.getActive();
var b = 0;
var wCell = 'Work';
var x = new Date('3/16/2013 0:00:00');
for(var i=2;i<=lastRow;i++){
ss.getRange(i,10).activate();
if(x.setHours(0,0,0,0) == ss.getRange(i, 17).getValue() && 'Work' == ss.getRange(i, 5).getValues()){
ss.getRange(i, 10).setValue('Unexcused Absence');
}
}
javascript datetime google-apps-script google-sheets
edited Nov 20 '18 at 23:15
Rubén
10.7k43568
asked Nov 20 '18 at 17:35
YuheYuhe
11
marked as duplicate by TheMaster, Community♦ Nov 22 '18 at 17:12
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
-1
This question already has an answer here:
Compare two dates with JavaScript
35 answers
I have issues with a cell in google apps script where if a cell = "0:00" it needs to equal some text like "Unexcused Absence". when I run the code it gives me a blank cell.
function Boh() {
var sheet = SpreadsheetApp;
var ss = sheet.getActiveSpreadsheet().getActiveSheet();
var lastRow = ss.getLastRow();
var spreadsheet = SpreadsheetApp.getActive();
var b = 0;
var wCell = 'Work';
var x = new Date('3/16/2013 0:00:00');
for(var i=2;i<=lastRow;i++){
ss.getRange(i,10).activate();
if(x.setHours(0,0,0,0) == ss.getRange(i, 17).getValue() && 'Work' == ss.getRange(i, 5).getValues()){
ss.getRange(i, 10).setValue('Unexcused Absence');
}
}
javascript datetime google-apps-script google-sheets
edited Nov 20 '18 at 23:15
Rubén
10.7k43568
asked Nov 20 '18 at 17:35
YuheYuhe
11
marked as duplicate by TheMaster, Community♦ Nov 22 '18 at 17:12
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
How are you running this script? Is it supposed to be an onEdit(e) type trigger or run from menu or button?
– TheWizEd
Nov 20 '18 at 17:42
Hi, I'm running this on Macros on GoogleSheets by using the menu. On cell "Q" is all time-frames and cell "E" shows the type of day Work/Off and Cell "J" is where I want to print the answer if true.
– Yuhe
Nov 20 '18 at 17:50
Found the answer: Needed to use**.getDisplayValues()**returns the value as you see in the screen, therefore always a string. And now it works, thanks for the help anyway.
– Yuhe
Nov 22 '18 at 17:09
add a comment |
-1
-1
-1
1
This question already has an answer here:
Compare two dates with JavaScript
35 answers
I have issues with a cell in google apps script where if a cell = "0:00" it needs to equal some text like "Unexcused Absence". when I run the code it gives me a blank cell.
function Boh() {
var sheet = SpreadsheetApp;
var ss = sheet.getActiveSpreadsheet().getActiveSheet();
var lastRow = ss.getLastRow();
var spreadsheet = SpreadsheetApp.getActive();
var b = 0;
var wCell = 'Work';
var x = new Date('3/16/2013 0:00:00');
for(var i=2;i<=lastRow;i++){
ss.getRange(i,10).activate();
if(x.setHours(0,0,0,0) == ss.getRange(i, 17).getValue() && 'Work' == ss.getRange(i, 5).getValues()){
ss.getRange(i, 10).setValue('Unexcused Absence');
}
}
javascript datetime google-apps-script google-sheets
edited Nov 20 '18 at 23:15
Rubén
10.7k43568
asked Nov 20 '18 at 17:35
YuheYuhe
11
This question already has an answer here:
Compare two dates with JavaScript
35 answers
I have issues with a cell in google apps script where if a cell = "0:00" it needs to equal some text like "Unexcused Absence". when I run the code it gives me a blank cell.
function Boh() {
var sheet = SpreadsheetApp;
var ss = sheet.getActiveSpreadsheet().getActiveSheet();
var lastRow = ss.getLastRow();
var spreadsheet = SpreadsheetApp.getActive();
var b = 0;
var wCell = 'Work';
var x = new Date('3/16/2013 0:00:00');
for(var i=2;i<=lastRow;i++){
ss.getRange(i,10).activate();
if(x.setHours(0,0,0,0) == ss.getRange(i, 17).getValue() && 'Work' == ss.getRange(i, 5).getValues()){
ss.getRange(i, 10).setValue('Unexcused Absence');
}
}
This question already has an answer here:
Compare two dates with JavaScript
35 answers
javascript datetime google-apps-script google-sheets
javascript datetime google-apps-script google-sheets
edited Nov 20 '18 at 23:15
Rubén
10.7k43568
asked Nov 20 '18 at 17:35
YuheYuhe
11
edited Nov 20 '18 at 23:15
Rubén
10.7k43568
asked Nov 20 '18 at 17:35
YuheYuhe
11
edited Nov 20 '18 at 23:15
Rubén
10.7k43568
edited Nov 20 '18 at 23:15
Rubén
10.7k43568
edited Nov 20 '18 at 23:15
Rubén
10.7k43568
10.7k43568
asked Nov 20 '18 at 17:35
YuheYuhe
11
asked Nov 20 '18 at 17:35
YuheYuhe
11
asked Nov 20 '18 at 17:35
YuheYuhe
11
11
marked as duplicate by TheMaster, Community♦ Nov 22 '18 at 17:12
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by TheMaster, Community♦ Nov 22 '18 at 17:12
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
How are you running this script? Is it supposed to be an onEdit(e) type trigger or run from menu or button?
– TheWizEd
Nov 20 '18 at 17:42
Hi, I'm running this on Macros on GoogleSheets by using the menu. On cell "Q" is all time-frames and cell "E" shows the type of day Work/Off and Cell "J" is where I want to print the answer if true.
– Yuhe
Nov 20 '18 at 17:50
Found the answer: Needed to use**.getDisplayValues()**returns the value as you see in the screen, therefore always a string. And now it works, thanks for the help anyway.
– Yuhe
Nov 22 '18 at 17:09
add a comment |
How are you running this script? Is it supposed to be an onEdit(e) type trigger or run from menu or button?
– TheWizEd
Nov 20 '18 at 17:42
Hi, I'm running this on Macros on GoogleSheets by using the menu. On cell "Q" is all time-frames and cell "E" shows the type of day Work/Off and Cell "J" is where I want to print the answer if true.
– Yuhe
Nov 20 '18 at 17:50
Found the answer: Needed to use**.getDisplayValues()**returns the value as you see in the screen, therefore always a string. And now it works, thanks for the help anyway.
– Yuhe
Nov 22 '18 at 17:09
How are you running this script? Is it supposed to be an onEdit(e) type trigger or run from menu or button?
– TheWizEd
Nov 20 '18 at 17:42
How are you running this script? Is it supposed to be an onEdit(e) type trigger or run from menu or button?
– TheWizEd
Nov 20 '18 at 17:42
Hi, I'm running this on Macros on GoogleSheets by using the menu. On cell "Q" is all time-frames and cell "E" shows the type of day Work/Off and Cell "J" is where I want to print the answer if true.
– Yuhe
Nov 20 '18 at 17:50
Hi, I'm running this on Macros on GoogleSheets by using the menu. On cell "Q" is all time-frames and cell "E" shows the type of day Work/Off and Cell "J" is where I want to print the answer if true.
– Yuhe
Nov 20 '18 at 17:50
Found the answer: Needed to use
**.getDisplayValues()** returns the value as you see in the screen, therefore always a string. And now it works, thanks for the help anyway.– Yuhe
Nov 22 '18 at 17:09
Found the answer: Needed to use
**.getDisplayValues()** returns the value as you see in the screen, therefore always a string. And now it works, thanks for the help anyway.– Yuhe
Nov 22 '18 at 17:09
add a comment |
1 Answer
1
active
oldest
votes
0
I don't use recorded macros so not sure how that works. This will add a menu item to the main menu and you can run the script from there. Try this.
function onOpen() {
var thisMenu = SpreadsheetApp.getUi().createMenu('Boh');
thisMenu.addItem('Boh', 'Boh').addToUi();
}
function Boh() {
try {
var spread = SpreadsheetApp.getActiveSpreadsheet();
// Be sure you are on the right sheet when you run this
var sheet = spread.getActiveSheet();
// Get everything
var values = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
// results will be put in column J later
var results = ;
// Skip row 1
for(var i=1; i<values.length; i++ ) {
// Assuming ss.getRange(i,17).getValue() is a duration, column 17 or Q becomes values[i][16]
if( ( Utilities.formatDate(values[i][16],Session.getScriptTimeZone(),"HH:mm:ss") === "00:00:00" ) &&
( values[i][4] === "Work" ) ) {
results.push(["Unexcused Absence"]);
}
else {
// Notice I push an array to make results a 2D array of n rows 1 column
// values[i][9] is column 10 or J
results.push([""]);
}
}
// Note we skipped line 1
sheet.getRange(2,10,results.length,1).setValues(results);
}
catch(err) {
Logger.log(err);
}
}
answered Nov 20 '18 at 18:11
TheWizEdTheWizEd
560147
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
I don't use recorded macros so not sure how that works. This will add a menu item to the main menu and you can run the script from there. Try this.
function onOpen() {
var thisMenu = SpreadsheetApp.getUi().createMenu('Boh');
thisMenu.addItem('Boh', 'Boh').addToUi();
}
function Boh() {
try {
var spread = SpreadsheetApp.getActiveSpreadsheet();
// Be sure you are on the right sheet when you run this
var sheet = spread.getActiveSheet();
// Get everything
var values = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
// results will be put in column J later
var results = ;
// Skip row 1
for(var i=1; i<values.length; i++ ) {
// Assuming ss.getRange(i,17).getValue() is a duration, column 17 or Q becomes values[i][16]
if( ( Utilities.formatDate(values[i][16],Session.getScriptTimeZone(),"HH:mm:ss") === "00:00:00" ) &&
( values[i][4] === "Work" ) ) {
results.push(["Unexcused Absence"]);
}
else {
// Notice I push an array to make results a 2D array of n rows 1 column
// values[i][9] is column 10 or J
results.push([""]);
}
}
// Note we skipped line 1
sheet.getRange(2,10,results.length,1).setValues(results);
}
catch(err) {
Logger.log(err);
}
}
answered Nov 20 '18 at 18:11
TheWizEdTheWizEd
560147
add a comment |
0
I don't use recorded macros so not sure how that works. This will add a menu item to the main menu and you can run the script from there. Try this.
function onOpen() {
var thisMenu = SpreadsheetApp.getUi().createMenu('Boh');
thisMenu.addItem('Boh', 'Boh').addToUi();
}
function Boh() {
try {
var spread = SpreadsheetApp.getActiveSpreadsheet();
// Be sure you are on the right sheet when you run this
var sheet = spread.getActiveSheet();
// Get everything
var values = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
// results will be put in column J later
var results = ;
// Skip row 1
for(var i=1; i<values.length; i++ ) {
// Assuming ss.getRange(i,17).getValue() is a duration, column 17 or Q becomes values[i][16]
if( ( Utilities.formatDate(values[i][16],Session.getScriptTimeZone(),"HH:mm:ss") === "00:00:00" ) &&
( values[i][4] === "Work" ) ) {
results.push(["Unexcused Absence"]);
}
else {
// Notice I push an array to make results a 2D array of n rows 1 column
// values[i][9] is column 10 or J
results.push([""]);
}
}
// Note we skipped line 1
sheet.getRange(2,10,results.length,1).setValues(results);
}
catch(err) {
Logger.log(err);
}
}
answered Nov 20 '18 at 18:11
TheWizEdTheWizEd
560147
add a comment |
0
0
0
I don't use recorded macros so not sure how that works. This will add a menu item to the main menu and you can run the script from there. Try this.
function onOpen() {
var thisMenu = SpreadsheetApp.getUi().createMenu('Boh');
thisMenu.addItem('Boh', 'Boh').addToUi();
}
function Boh() {
try {
var spread = SpreadsheetApp.getActiveSpreadsheet();
// Be sure you are on the right sheet when you run this
var sheet = spread.getActiveSheet();
// Get everything
var values = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
// results will be put in column J later
var results = ;
// Skip row 1
for(var i=1; i<values.length; i++ ) {
// Assuming ss.getRange(i,17).getValue() is a duration, column 17 or Q becomes values[i][16]
if( ( Utilities.formatDate(values[i][16],Session.getScriptTimeZone(),"HH:mm:ss") === "00:00:00" ) &&
( values[i][4] === "Work" ) ) {
results.push(["Unexcused Absence"]);
}
else {
// Notice I push an array to make results a 2D array of n rows 1 column
// values[i][9] is column 10 or J
results.push([""]);
}
}
// Note we skipped line 1
sheet.getRange(2,10,results.length,1).setValues(results);
}
catch(err) {
Logger.log(err);
}
}
answered Nov 20 '18 at 18:11
TheWizEdTheWizEd
560147
I don't use recorded macros so not sure how that works. This will add a menu item to the main menu and you can run the script from there. Try this.
function onOpen() {
var thisMenu = SpreadsheetApp.getUi().createMenu('Boh');
thisMenu.addItem('Boh', 'Boh').addToUi();
}
function Boh() {
try {
var spread = SpreadsheetApp.getActiveSpreadsheet();
// Be sure you are on the right sheet when you run this
var sheet = spread.getActiveSheet();
// Get everything
var values = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
// results will be put in column J later
var results = ;
// Skip row 1
for(var i=1; i<values.length; i++ ) {
// Assuming ss.getRange(i,17).getValue() is a duration, column 17 or Q becomes values[i][16]
if( ( Utilities.formatDate(values[i][16],Session.getScriptTimeZone(),"HH:mm:ss") === "00:00:00" ) &&
( values[i][4] === "Work" ) ) {
results.push(["Unexcused Absence"]);
}
else {
// Notice I push an array to make results a 2D array of n rows 1 column
// values[i][9] is column 10 or J
results.push([""]);
}
}
// Note we skipped line 1
sheet.getRange(2,10,results.length,1).setValues(results);
}
catch(err) {
Logger.log(err);
}
}
answered Nov 20 '18 at 18:11
TheWizEdTheWizEd
560147
edited Nov 20 '18 at 18:17
answered Nov 20 '18 at 18:11
TheWizEdTheWizEd
560147
answered Nov 20 '18 at 18:11
TheWizEdTheWizEd
560147
answered Nov 20 '18 at 18:11
TheWizEdTheWizEd
560147
560147
add a comment |
add a comment |
How are you running this script? Is it supposed to be an onEdit(e) type trigger or run from menu or button?
– TheWizEd
Nov 20 '18 at 17:42
Hi, I'm running this on Macros on GoogleSheets by using the menu. On cell "Q" is all time-frames and cell "E" shows the type of day Work/Off and Cell "J" is where I want to print the answer if true.
– Yuhe
Nov 20 '18 at 17:50
Found the answer: Needed to use
**.getDisplayValues()**returns the value as you see in the screen, therefore always a string. And now it works, thanks for the help anyway.– Yuhe
Nov 22 '18 at 17:09