Google Table Search Date an Set Value in different row

In Google Sheets, when the sheet opens I need to check if the Date in Row "E" is today or older than today.

If this condition is true I need to write "DONE" in Column "B". I was able to script this so far on startup.

But this function doesn´t match the correct row. It is still not working for me :(

function Statuspruefen(){

  var sheet = SpreadsheetApp.getActiveSheet();

  var date = sheet.getRange("F2").setValue(new Date()).getValue();

  var daterange = sheet.getRange("E2:E").getValues()

  for(var i=0; i<daterange.length; i++) {

    if(new Date(daterange[i]).setHours(0,0,0,0) <= date.setHours(0,0,0,0)) {

      var today_row = (i+3);

      var today_set = sheet.getRange("B2"-today_row).setValue("DONE"); 

    }

  }

Example Table

Can anyone help?

edited Nov 14 '18 at 13:56

asked Nov 13 '18 at 14:03

Thomas Hartmann

add a comment |

In Google Sheets, when the sheet opens I need to check if the Date in Row "E" is today or older than today.

If this condition is true I need to write "DONE" in Column "B". I was able to script this so far on startup.

But this function doesn´t match the correct row. It is still not working for me :(

function Statuspruefen(){

  var sheet = SpreadsheetApp.getActiveSheet();

  var date = sheet.getRange("F2").setValue(new Date()).getValue();

  var daterange = sheet.getRange("E2:E").getValues()

  for(var i=0; i<daterange.length; i++) {

    if(new Date(daterange[i]).setHours(0,0,0,0) <= date.setHours(0,0,0,0)) {

      var today_row = (i+3);

      var today_set = sheet.getRange("B2"-today_row).setValue("DONE"); 

    }

  }

Example Table

Can anyone help?

edited Nov 14 '18 at 13:56

asked Nov 13 '18 at 14:03

Thomas Hartmann

add a comment |

In Google Sheets, when the sheet opens I need to check if the Date in Row "E" is today or older than today.

If this condition is true I need to write "DONE" in Column "B". I was able to script this so far on startup.

But this function doesn´t match the correct row. It is still not working for me :(

function Statuspruefen(){

  var sheet = SpreadsheetApp.getActiveSheet();

  var date = sheet.getRange("F2").setValue(new Date()).getValue();

  var daterange = sheet.getRange("E2:E").getValues()

  for(var i=0; i<daterange.length; i++) {

    if(new Date(daterange[i]).setHours(0,0,0,0) <= date.setHours(0,0,0,0)) {

      var today_row = (i+3);

      var today_set = sheet.getRange("B2"-today_row).setValue("DONE"); 

    }

  }

Example Table

Can anyone help?

edited Nov 14 '18 at 13:56

asked Nov 13 '18 at 14:03

Thomas Hartmann

In Google Sheets, when the sheet opens I need to check if the Date in Row "E" is today or older than today.

If this condition is true I need to write "DONE" in Column "B". I was able to script this so far on startup.

But this function doesn´t match the correct row. It is still not working for me :(

function Statuspruefen(){

  var sheet = SpreadsheetApp.getActiveSheet();

  var date = sheet.getRange("F2").setValue(new Date()).getValue();

  var daterange = sheet.getRange("E2:E").getValues()

  for(var i=0; i<daterange.length; i++) {

    if(new Date(daterange[i]).setHours(0,0,0,0) <= date.setHours(0,0,0,0)) {

      var today_row = (i+3);

      var today_set = sheet.getRange("B2"-today_row).setValue("DONE"); 

    }

  }

Example Table

Can anyone help?

date find compare

edited Nov 14 '18 at 13:56

asked Nov 13 '18 at 14:03

Thomas Hartmann

edited Nov 14 '18 at 13:56

asked Nov 13 '18 at 14:03

Thomas Hartmann

edited Nov 14 '18 at 13:56

asked Nov 13 '18 at 14:03

Thomas Hartmann

asked Nov 13 '18 at 14:03

Thomas Hartmann

asked Nov 13 '18 at 14:03

Thomas Hartmann

add a comment |

1 Answer
1

active

oldest

votes

i found the solution :)

var sheet     = SpreadsheetApp.getActiveSheet();

var date      = sheet.getRange("F2").setValue(new Date()).getValue();

var daterange = sheet.getRange("E2:E").getValues()

for(var i=0; i<daterange.length; i++) {

  if(new Date(daterange[i]).setHours(0,0,0,0) <= date.setHours(0,0,0,0)) {

      var today_row = (i+2);

      var today_set = sheet.getRange("B2"+today_row).setValue("DONE");

    }

}

answered Nov 21 '18 at 11:46

Thomas Hartmann

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53282747%2fgoogle-table-search-date-an-set-value-in-different-row%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

i found the solution :)

var sheet     = SpreadsheetApp.getActiveSheet();

var date      = sheet.getRange("F2").setValue(new Date()).getValue();

var daterange = sheet.getRange("E2:E").getValues()

for(var i=0; i<daterange.length; i++) {

  if(new Date(daterange[i]).setHours(0,0,0,0) <= date.setHours(0,0,0,0)) {

      var today_row = (i+2);

      var today_set = sheet.getRange("B2"+today_row).setValue("DONE");

    }

}

answered Nov 21 '18 at 11:46

Thomas Hartmann

add a comment |

i found the solution :)

var sheet     = SpreadsheetApp.getActiveSheet();

var date      = sheet.getRange("F2").setValue(new Date()).getValue();

var daterange = sheet.getRange("E2:E").getValues()

for(var i=0; i<daterange.length; i++) {

  if(new Date(daterange[i]).setHours(0,0,0,0) <= date.setHours(0,0,0,0)) {

      var today_row = (i+2);

      var today_set = sheet.getRange("B2"+today_row).setValue("DONE");

    }

}

answered Nov 21 '18 at 11:46

Thomas Hartmann

add a comment |

i found the solution :)

var sheet     = SpreadsheetApp.getActiveSheet();

var date      = sheet.getRange("F2").setValue(new Date()).getValue();

var daterange = sheet.getRange("E2:E").getValues()

for(var i=0; i<daterange.length; i++) {

  if(new Date(daterange[i]).setHours(0,0,0,0) <= date.setHours(0,0,0,0)) {

      var today_row = (i+2);

      var today_set = sheet.getRange("B2"+today_row).setValue("DONE");

    }

}

answered Nov 21 '18 at 11:46

Thomas Hartmann

i found the solution :)

var sheet     = SpreadsheetApp.getActiveSheet();

var date      = sheet.getRange("F2").setValue(new Date()).getValue();

var daterange = sheet.getRange("E2:E").getValues()

for(var i=0; i<daterange.length; i++) {

  if(new Date(daterange[i]).setHours(0,0,0,0) <= date.setHours(0,0,0,0)) {

      var today_row = (i+2);

      var today_set = sheet.getRange("B2"+today_row).setValue("DONE");

    }

}

answered Nov 21 '18 at 11:46

Thomas Hartmann

answered Nov 21 '18 at 11:46

Thomas Hartmann

answered Nov 21 '18 at 11:46

Thomas Hartmann

answered Nov 21 '18 at 11:46

Thomas Hartmann

add a comment |

draft saved

draft discarded

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ndtyjky