How do I stop script from reading rows more than once?

Question

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

Answer 1

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 declare getRange(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 test data[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.

---

function wa14198102() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Test Weekend Coverage/Calendar Push - New');

  //Select the column we will check for the first blank cell
  var columnToCheck = sheet.getRange("A:A").getValues();
  // Get the last row based on the data range of a single column.
  var AlastRow = getLastRowSpecial(columnToCheck);  
  //TEST
  // Logger.log("DEBUG: the last row in column A = "+AlastRow);

  // get the data
  var datarange = sheet.getRange(AlastRow,1,1,7);
  //Logger.log("DEBUG: the range is "+datarange.getA1Notation())
  var data = datarange.getValues();
  //Logger.log(data); // DEBUG

  //Logger.log("DEBUG: Published value = "+data[0][6]);

  if(data[0][6]!= 'Published'){
    //Logger.log("DEBUG: the IF is true")
    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 published = data[0][6];
    var masterCal = CalendarApp.getCalendarById('<<enter your calendar ID>>');
    masterCal.createEvent(title,new Date(startDate),new Date(endDate),{location:eventLoca,description:eventDesc,guests:attendees,sendInvites:true});  
    sheet.getRange(AlastRow,7).setValue('Published');
    //Logger.log("Updated Published status");
  }
  else{
   //Logger.log("DEBUG: the IF is false; do nothing")
  }
}  

/************************************************************************
 *
 * Gets the last row number based on a selected column range values
 *
 * @param {array} range : takes a 2d array of a single column's values
 *
 * @returns {number} : the last row number with a value. 
 *
 */ 
// Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes
// Credit: https://yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/


function getLastRowSpecial(range){
  var rowNum = 0;
  var blank = false;
  for(var row = 0; row < range.length; row++){

    if(range[row][0] === "" && !blank){
      rowNum = row;
      blank = true;

    }else if(range[row][0] !== ""){
      blank = false;
    };
  };
  return rowNum;
};