1

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});  
}
  • 1
    Welcome. When the script is run for the second time, what value is returned by the Logger statement? – Tedinoz May 3 '20 at 11:47
  • How are you calling the script? Is the script bound to the same spreadsheet to the one you are updating? – Rubén May 3 '20 at 16:27
  • Hey, Ruben. it is bound to the sheet I am making updates to. I just added a link to the sheet in my original post. It is rather bare because I am testing it, but the general idea is there if you want to view it. Edit access has been provided as well. – Lucas Hammonds May 3 '20 at 18:40
  • 1
    It would have been helpful to explain that the data on "Test Weekend Coverage/Calendar Push - New" is populated by a query, AND that the query only refers to six rows of data AND that some fields on the source data sheet are populated by non-spill formulae AND that the attendees email references a THIRD sheet! – Tedinoz May 4 '20 at 12:28
  • 1
    You also changed your script without warning anyone. It used to have a line if(data[6]!= 'Published') as well as sheet.getRange(lastRow,7).setValue('Published'); – Tedinoz May 4 '20 at 12:39
0

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;
};
  • This is spot on and makes sense. And good eye on seeing the query issue. I was going to inquire about this as well, but wanted to sort the initial ask out first. Finally, I upvoted this answer, but i do not have the rep to show them publicly. Thanks again. – Lucas Hammonds May 4 '20 at 22:05
  • @LucasHammonds. as the user who asked the question, you can Accept the answer. The link explains how. – Tedinoz May 5 '20 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.