CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-calendar-api
Marcelo Fritz
Marcelo Fritz

Reputation: 3

Create events with the data from the last row of the spreadsheet

I'm having trouble stopping or even selecting the correct data to automate a routine.

The intent of my code is to use data from a Google Sheet to create events on Google Calendar. However, my current code generates events for all the data in the spreadsheet and I only need it to generate for the last row (because I've already managed to set a trigger for the code to run as soon as a form is submitted)

I know it's okay to use getLastRow, but I'm having syntax difficulties. Can anybody help me?

I have found solutions that are more than excellent, but very complex and extensive. In short, I just want to create events with the data from the last row of the spreadsheet.

Thank you very much in advance!

var app=SpreadsheetApp;
var calendar=CalendarApp.getCalendarById('[email protected]');
var sheet=app.getActiveSheet();

//calendar
function myCalendar()
{
  var range=sheet.getRange('A2:I').getValues();
  range.map(function(elem,ind,obj){
    if(elem[0]!=""){
      calendar.createEvent(elem[1]+" - "+elem[2], elem[3], elem[4])
      }
  })
}

Upvotes: 0

Views: 228

Answers (2)

Wicket
Wicket

Reputation: 38218

Your code is creating multiple events but you only one to create one based on the values of the last row.

If the above is correct, the code doesn't require to loop through all the rows. In the current code that is done by using Array.prototype.map.

Minimal change

var app=SpreadsheetApp;
var calendar=CalendarApp.getCalendarById('[email protected]');
var sheet=app.getActiveSheet();

//calendar
function myCalendar()
{
  var range=sheet.getDataRange().getValues();
  var elem = range.pop();
  calendar.createEvent(elem[1]+" - "+elem[2], elem[3], elem[4])

}
  • getDataRange returns the data range (from A1 to the intersection of the last column and row having a value.
  • Array.prototype.pop() returns the last element of an array an remove that element from the array.

Upvotes: 0

Cooper
Cooper

Reputation: 64072

function myCalendar() {
  const calendar = CalendarApp.getCalendarById('[email protected]');
  const sheet = SpreadsheetApp.getActiveSheet();
  const vs = sheet.getRange(2, 1, sheet.getLastRow() - 1, 9).getValues();
  vs.forEach(r => {
    if (r[0] != "") {
      calendar.createEvent(r[1] + " - " + r[2], r[3], r[4])
    }
  });
}

Upvotes: 1

Related Questions