Running into an issue where the script is reading the same row more than once. For example, I run the script and it pushes row 1 to the calendar as intended. When I add more info in row 2 and run the script again, it will push info from row 1 again as well as row 2's info to the calendar. I have tried an already added function as well as adding another column I can mark once completed so the script is contained to new rows only. I am still a novice when it comes to scripts. Working only with simple scripts and manipulating them to suit the needs of the sheet. When it comes to adding/creating functions in a script is where I run into issues.
Here is the code and the link to the sheet.
function simpleSheetsToCalendar() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Test Weekend Coverage/Calendar Push - New');
var lastRow = sheet.getLastRow();
Logger.log(lastRow);
var data = sheet.getRange(sheet.getLastRow(),1,1,6).getValues();
var title = data[0][0];
var eventDesc = data[0][1];
var eventLoca = data[0][2];
var startDate = data[0][3];
var endDate = data[0][4];
var attendees = data[0][5];
var masterCal = CalendarApp.getCalendarById('doordash.com_oi7od81qlvove73q1dgt013838@group.calendar.google.com');
masterCal.createEvent(title,new Date(startDate),new Date(endDate),{location:eventLoca,description:eventDesc,guests:attendees,sendInvites:true});
}
Best Answer
You are creating calendar events from a sheet="Test Weekend Coverage/Calendar Push - New" and you find that it is consistently re-processing existing entries instead of the last entry.
The calendar sheet is populated by a series of queries sourced from another sheet "Non-Grocery Join Sheet". "Non-Grocery Join Sheet" holds the raw entries for the calendar, but no matter how many rows of data may appear on this sheet, the query will limit the range to 6 rows.
The answer for this topic consists of three elements:
1 - corrections to syntax in the OP script,
2 - enabling the query to display all the entries on "Non-Grocery Join Sheet".
3 - a utility that identifies the last row of content when a query is the source range for a script.
Syntax
There are three syntax errors in OP script.
1 -
getRange(lastRow,1,1,7)
.getRange()
is an index-based method. Column A = 1, Column F = 6, Column G = 7. The "Published" column is Column G; so you want a range that will span column A to Column G, you need to declaregetRange(lastRow,1,1,7)
.2 -
if(data[0][6]!= 'Published')
.getValues()
returns a zero-based two-dimensional array. In order to test the value in Column F, one must testdata[0][6]
.data[6]
will return "undefined" and kill the script.3 -
if... else
and{ ... }
block statements.The script as written does not describe any action in the event that the 'IF" statement is false. The MDN Javascript Web Docs suggests that when executing multiple statements within a clause, use a block statement ({ ... }) to group those statements, and that it is a good practice to always use block statements.
Troubleshooting
Sometimes, logging the value of certain variables will help identify values, and there, errors. In the answer below, I have left
Logger.log
statements in the code for your information.Query statements
=QUERY('Non-Grocery Join Sheet'!L2:L)
By defining a range as the entire column (except the for header row), the query range will always display extra rows added to the source sheet. This should be done for each query statement, or use a single query for the entire calendar sheet.
Last row of Content
When a query is subject of a
getLastRow
method, the reported last row might not be the actual last row of content because the query will display as many rows as exist in the source sheet. It is more reliable to analyse the actual data until the last row is found.In the answer, a utility described by https://yagisanatode.com/ is used to determine the last row.