CODEWITHSUNDEEP
google-apps-scriptgoogle-calendar-api
Mary
Mary

Reputation: 1

How can I import the last row ONLY of a spreadsheet to my calendar using Google Apps Script?

I have been able to create a script to import a spreadsheet (delivery schedule) to a calendar. It works great, but I am constantly adding new deliveries to the schedule. When I import it to the calendar, it imports everything every time. Is there a way to have ONLY the last row imported and not the whole spreadsheet?

This is the script that I am using...

var DELIVERY_IMPORTED = "DELIVERY_IMPORTED";
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
var menuEntries = [{name: "Import Events in Calendar", functionName: "importCalendar"}];
ss.addMenu("Calendar", menuEntries);
}

function importCalendar() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;  // First row of data to process
var numRows = 5;   // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 5)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var title = row[1]; // Name
var location = row[2]; 
var startDate = row[3]; // Start Date
var description = row[4]; 
var cal = CalendarApp.getCalendarsByName('Calendar Name')[0];
var advancedArgs = {description: description, location: location};
cal.createAllDayEvent(title, new Date(startDate), advancedArgs);
  SpreadsheetApp.flush();  
}
}

Any help would be greatly appreciated!

Upvotes: 0

Views: 170

Answers (1)

sulDu
sulDu

Reputation: 31

If you only have one new lime each time, you can use Sheet.getLastRow() to get the index of the last row with content.

Else you might use Properties service to store the last imported row, and start from there.

Upvotes: 1

Related Questions