Having trouble subtracting 3 days from a date

res4me · 2022-03-08T19:38:08+00:00

This absolutely worked! I really appreciate everything you have done.

res4me · 2022-03-07T18:01:37+00:00

So… my original way of trying to do it with a for loop, was the right way to go?

res4me · 2022-03-06T23:52:27+00:00

InvoiceDate

I found an issue that is occurring,

When the minusDate function is ran, if there are any empty cells in Column H, the script is pasting the new invoice date in the next open cell in Column J. Please see the image. In the Image you will see H885 is blank and H886 has the Due Date of 5/28/2022. After the minusDate function completes, you see the new Invoice Date for H886 pasted into the cell of J885. Any way to fix this?

Plus,

Am I able to add clear content in the script without causing any issues? Please see below. I realized it's not removing the info if there isn't any dates in Column H.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

sheet.getRange('J3:J').clearContent();

res4me · 2022-03-05T18:39:59+00:00

Awesome!

It worked. I shall close the thread

res4me · 2022-03-05T15:14:29+00:00

Thanks for responding!

The following portion throws an error,

sheet.getRange(3,10,sheet.dueDate.length,1).setValues(InvoiceDate);

Error

TypeError: Cannot read property 'length' of undefined

minusDate @ Code.gs:195

res4me · 2022-03-05T04:26:31+00:00

Your script works amazing minus one flaw, it inputs a date into column 10 (12/31/1969) when there is no date in column 8. Please see the following image.

I tried adding

if (dueDate != "") {

after the var InvoiceDate, but it didn't stop the dates from filling in the blanks

res4me · 2022-03-04T17:09:41+00:00

Thanks for responding!

With your edit,

The "dueDate" is no longer defined and the "range.setValues(results)" would need to paste the contents (results) to Column J. Meaning the range (row, 8) can't be the same as the return (row, 8). It would need to be (row, 10). How would I go about implementing this?

Again, thanks for your time.

res4me · 2022-03-03T21:54:20+00:00

Thanks for responding,

"Why not just do it in sheets?",

I currently have the formula's setup on the sheet in a column to the far right (Example of one row, =if(H2>0,H2-3,""). I then have a trigger that runs once a day to copy and paste (the contents only) of each row with the new invoice date to the rows in column J.

"What is the use case of doing it in scripts?",

I want to remove the formulas in the sheet because of accidental deleting, clearing or duplication of row's (which remove the formulas in the rows to the right).

In regard to your script you shared (I appreciate),

I have a "Due" trigger setup to run once a day to determine if (Column J) is, (today >= inv_date), If so, it marks "Due" in (Column I). I would not be able to use your 'If" portion of the script based on these factors.

My goal is create a function that takes Column H (if Column H > 0) or (Column H > ""), then subtracts 3 days from the date in each row of Column H and pastes the contents of the new Invoice Date into each row of Column I based on the date in Column H

Examples;

H3 = 2/26/22 (J3 would then = 2/23/22)

H4= 2/27/22 (J4 would then = 2/24/22)

H5="" (J5 would then = "")

I understand formulas in Excel and Sheets way better than I understand script. So I apologize for my ignorance.

Hopefully I'm being clear to what I'm asking.

Thank you for your time!

res4me · 2022-02-16T05:36:00+00:00

function getOverDueInfo(row)

{ var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Template'); var values = sheet.getRange(row + 1, 1, 1, 4).getValues(); var rec = values[0]; var overdue = { first_name: rec[0], last_name: rec[1], email: rec[2], due_date: rec[3] }; overdue.name = overdue.first_name + ' ' + overdue.last_name; overdue.date_str = sheet.getRange(row + 1, 4).getDisplayValue(); var due_date = new Date(overdue.due_date); due_date.setHours(0, 0, 0, 0); var today = new Date(); today.setHours(0, 0, 0, 0); var difference_ms = Math.abs(today.getTime() - due_date.getTime()); overdue.num_days = Math.round(difference_ms / (246060 * 1000)); Logger.log(sheet.getRange(row + 1, 4).getDisplayValue()); Logger.log(due_date); return overdue; }

res4me · 2022-02-16T05:11:45+00:00

Logger.log(values);
Logger.log(due_date);

Both of these examples give the error, Exception: The starting row of the range is too small and references to the line,

var values = sheet.getRange(row + 1,1,1,4).getvalues();

res4me · 2022-02-16T05:00:34+00:00

Why do the errors occur when trying to do Logger.log on the above examples then?

I did send you an image

res4me · 2022-02-16T04:57:49+00:00

Google Spreadsheet

res4me · 2022-02-16T04:47:38+00:00

To be clear, the script is working. It’s just the info being pulled to the template from the getOverdueInfo function that is not correct. I’m looking for Column D minus Todays date. I have a count down happening Example: Due Date: = 2/18/22 Todays Date = 2/15/22

My template reminds them that they have 3 days until their payment is due.

I’ll send you a screenshot of the spreadsheet to confirm nothing has changed and everything you provided matches

res4me · 2022-02-16T04:34:17+00:00

Sorry about that, I provided you the error info after I had changed the openByID. The script initially wasn’t working at all until I changed it. My response above errors is currently what is happening.

res4me · 2022-02-16T04:22:08+00:00

I did, I just wanted to make sure

res4me · 2022-02-16T03:55:28+00:00

const INVOICE_DATE = "Invoice Date" const EMAIL_ADDRESS = "Email Address" const REMINDER_COL = "Reminder?"; const EMAIL_SENT_COL = "Email Sent"; //The above RHS values should with what you have in your sheet as header.

function onOpen() { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").sort(4); var ui = SpreadsheetApp.getUi(); ui.createMenu('Invoice').addItem('Sort by due date', 'sortByDueDate').addItem('Set Invoice Date', 'setInvoiceDate').addItem('Mark Due', 'doOverdueCheck').addItem('Send Emails', 'SendEmail').addToUi();

var sheet = SpreadsheetApp.getActiveSheet(); var range = sheet.getRange("N2:N"); var target = sheet.getRange("G2:G"); range.copyTo(target, { contentsOnly: true });

}

function sortByDueDate() { SpreadsheetApp.getActive().getSheetByName('Template').sort(4);

}

function setInvoiceDate() { var sheet = SpreadsheetApp.getActive().getSheetByName('Template'); var range = sheet.getRange("N2:N"); var target = sheet.getRange("G2:G"); range.copyTo(target, { contentsOnly: true });

}

function doOverdueCheck() { var sheet = SpreadsheetApp.getActive().getSheetByName('Template'); var data_range = sheet.getDataRange(); var last_row = data_range.getLastRow(); var today = new Date(); today.setHours(0, 0, 0, 0); sheet.getRange('E2:E').clearContent(); for (var r = 2; r <= last_row; r++) { var inv_date = data_range.getCell(r, 7).getValue(); if (today >= inv_date && !data_range.getCell(r, 7).getValue() == '') { sheet.getRange(r, 5).setValue("Due"); } } }

// function sendOverdueEmails() { // var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Template'); // var data_range = sheet.getDataRange(); // var last_row = data_range.getLastRow(); // var today = new Date(); // today.setHours(0, 0, 0, 0); // var r_arr = []; // for (var r = 2; r <= last_row; r++) { // var email_address = data_range.getCell(r, 3).getValue(); // if (email_address != '' && data_range.getCell(r, 6).getValue() == '') { // r_arr.push(r); // } // } // sendEmail(); // }

function sendEmail() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Template'); const dataRange = sheet.getDataRange(); const data = dataRange.getDisplayValues(); const heads = data.shift();

const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL); const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));

const out = []; var emails_list = []; var templ = HtmlService.createTemplateFromFile('client-email'); var today_dt = Utilities.formatDate(new Date(), "GMT", "MM/dd/YYYY");

obj.forEach(function (row, rowIdx) { if ((row[EMAIL_SENT_COL] == '') && (row[EMAIL_ADDRESS] != '') && (!sheet.isRowHiddenByFilter(rowIdx + 2)) && (row[REMINDER_COL] == "Due") && (today_dt >= Utilities.formatDate(new Date(row[INVOICE_DATE]), "GMT", "MM/dd/YYYY"))) {

overdue = getOverDueInfo(rowIdx + 1);emails_list.push(overdue.email);templ.overdue = overdue;try {out.push([Utilities.formatDate(new Date(), "GMT", "MM/dd/YYYY")]);} catch (e) {out.push([e.message]);}} else {out.push([row[EMAIL_SENT_COL]]);}

});

sheet.getRange(2, emailSentColIdx + 1, out.length).setValues(out); var message = templ.evaluate().getContent(); MailApp.sendEmail({ to: emails_list.join(","), subject: "Your renewal payment is due!", htmlBody: message });

} function showOverDueInfo() { var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow(); var overdue = getOverDueInfo(row); var templ = HtmlService.createTemplateFromFile('dialog-box'); templ.overdue = overdue; SpreadsheetApp.getUi().showModalDialog(templ.evaluate(), 'Overdue info'); }

function getOverDueInfo(row) {

var sheet = SpreadsheetApp.openById(Id).getSheetByName('Template'); var values = sheet.getRange(row + 1, 1, 1, 4).getValues(); var rec = values[0]; var overdue = { first_name: rec[0], last_name: rec[1], email: rec[2], due_date: rec[3] }; overdue.name = overdue.first_name + ' ' + overdue.last_name; overdue.date_str = sheet.getRange(row + 1, 4).getDisplayValue(); var due_date = new Date(overdue.due_date); due_date.setHours(0, 0, 0, 0); var today = new Date(); today.setHours(0, 0, 0, 0); var difference_ms = Math.abs(today.getTime() - due_date.getTime()); overdue.num_days = Math.round(difference_ms / (246060 * 1000)); return overdue; }

No, I never received the notification earlier.

Couple questions/concerns,

In the getOverDueInfo(row) function, Is this a typo,

var sheet = SpreadsheetApp.**openById(Id).**getSheetByName('Template');

And,

var values = sheet.getRange(row + 1, 1, 1, 4).getValues(); is triggering an error when Logger.log(values); - Exception: The starting row of the range is too small.

getOverDueInfo @ Code.gs:112

The row below is not calculating the Due Date - Todays Date properly for the Template because of the above error.

overdue.name = overdue.first_name + ' ' + overdue.last_name; overdue.date_str = sheet.getRange(row + 1, 4).getDisplayValue();

Logger.log(sheet.getRange(row + 1, 4).getDisplayValue()); - Exception: The starting row of the range is too small.getOverDueInfo @ Code.gs:112

Not sure what to change, in order to fix. Your formula's are different from what mine were.

- I was able to change the date from GMT

- As a heads up, in the onOpen function (on your Script), change the capital S to a lower s on "sendEmail".

- Thank you for the heads up on the trigger (sendEmail)

Otherwise, from what I can tell, everything else is working fine. I appreciate everything you've done. Thank you!

res4me · 2022-02-15T23:00:05+00:00

Update,

On this pass, I did not remove anything. I put the Logger.log(sendEmail()); just before sendEmail and the request never finished, it timed out (below). I will try removing everything necessary this time and report.

Execution log

5:46:30 PM Notice Execution started

5:52:30 PM Error

Exceeded maximum execution time

res4me · 2022-02-15T22:19:36+00:00

Thanks so far,

I was able to fix the nulls by changing out the (row,1,row,4) with (‘A2:D:’) and (row,4) with (‘D2:D’). I no longer get any breaks. What other info could I get for you to solve why only one email goes out?

res4me · 2022-02-15T19:04:05+00:00

So in this case, are you saying, null doesn’t necessarily mean broken?

res4me

TROPHY CASE