Conditional format to run once on form submit

[Jeremy McCollom]

I created a google form that is the students Attendance/Assignment. The information is the timestamp, student email, class period, name and the responses to 2-3 questions (bell ringer). I have created an appscript that creates a conditional format on the timestamp if it's greater than today, i.e. > 3/2/2021 23:59:59, as well as sort by class period and then name. The problem I have is that everytime a student submits the form, it creates a repeat of the conditional format.

I would also like to not have to copy and paste the script for each daily form and have to convert my date to a number each time I use it. 

Any help would be greatly appreciated.

[Jean-Luc Vanhulst]

if you share a version of what you have now that would help understand and help you further?

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5262961a-07ca-4bb9-9dce-b94c39c3b8c6n%40googlegroups.com.

[Jeremy McCollom]

Thank you for the response, Here is what I have. It works well with the exception of running the conditional format every time a submission is made to the form.

/** @OnlyCurrentDoc */

 

function Datasort() {

  var spreadsheet = SpreadsheetApp.getActive();

  var sheet = spreadsheet.getActiveSheet();

  var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();

  conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()

  .setRanges([spreadsheet.getRange('A2:A104')])

  .whenNumberGreaterThan(44253.9999884259)

  .setBackground('#FFFF00')

  .build());

  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);

  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();

  spreadsheet.getActiveRange().offset(1, 0, spreadsheet.getActiveRange().getNumRows() - 1).sort({column: 4, ascending: true});

  spreadsheet.getActiveRange().offset(1, 0, spreadsheet.getActiveRange().getNumRows() - 1).sort([{column: 4, ascending: true}, {column: 5, ascending: true}]);

  spreadsheet.getRange('A2').activate();

};

 

Thank you for your assistance.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/ZhNHOwXYxbk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKTLZ51D3zsX%2B%3Drxed8QTdoOZdybg1XmTPAO-FjxGEFL-BJmjg%40mail.gmail.com.

[Jean-Luc Vanhulst]

Try this:

    .whenDateAfter(SpreadsheetApp.RelativeDate.TODAY)

and if you do 

  .setRanges([spreadsheet.getRange('A2:A')])

You cover the whole column and don't need to update.
you don't have to keep adding this condition (ie once you set this condition correct it should work for every future submission as well)

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CABUeSM1UaDMGdn_fxaFZixqEqA_sHOWG9Qe1keD_7sxCuSP-GQ%40mail.gmail.com.

[Kim Nilsson]

Sorry for asking perhaps a simple question about your first logic, but how would it be possible for a timestamp to be "greater than today"?

Or does that imply that any submission done after midnight 00:01 today?

I might have mixed up the logic between TODAY and NOW. Perhaps TODAY is fine, but never NOW?

[Bruce Mcpherson]

const tomorrow = new Date()
tomorrow.setDate(tomorrow.getDate() + 1)
tomorrow.setHours(0,0,0,0)

const someDate = new Date(2021,10,10)
if (someDate >= tomorrow) {
  console.log('its after today')
}
if (Date.now() < tomorrow) {
  console.log('its before tomorrow')
}

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/412301bb-4e08-4b39-b9f5-9d2e9c3aa87en%40googlegroups.com.

[Kim Nilsson]

Thank you.

[Bruce Mcpherson]

.. Kim I didn't see the preceding discussion context, only your question  on email - so  I gave you the JavaScript answer, (which of course may not be of much use - but I'll leave it here in case it helps anyone else)

[Kim Nilsson]

That's great.

Yes, I might have misunderstood the logic in the original question, but I still learnt from your response, so I'm happy. :-)

I haven't started using const yet, still holding on to var in my own scripts.

But, from what I have understood, replacing var with const would probably be a clean switch for any fairly simple code, right?

Kim

[Bruce Mcpherson]

In general, as long as you haven’t been relying on changes to bars within blocks,

Here’s a quick summary of gotchas

https://ramblings.mcpher.com/apps-script/apps-script-v8/javascript-v8-variable-scopes/

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a7UZd%3DzqbrUBP6gy-oUvx%2BrG5W%3Dby0bt3dqXES4BPXAxQ%40mail.gmail.com.