0

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!

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Mary
  • 1
  • 1
  • What if you have added TWO rows? You may want to look at [Create Google Calendar Events from Spreadsheet but prevent duplicates](http://stackoverflow.com/a/15790894/1677912). – Mogsdad Dec 04 '15 at 20:37
  • to keep it as simple as your script is, I'd recommend to simply colorize the rows that are already exported to your calendar. Its simple (`setBackground('#red')` for example) and very "visual". Then process only the rows that are not colorized yet. I think only a couple of lines added to your code will do the job. If you want something more sophisticated and well written then Mogsdad reference (see above) will be the solution ;-) note please remove this `SpreadsheetApp.flush();` , it's useless here and use `getLastRow()` to get the number of rows to process – Serge insas Dec 04 '15 at 20:52
  • Thanks for the great suggestions. I did however go with Mogsdad's suggestion and with a little tweaking it works fabulously. Thanks again for the help!! =D – Mary Dec 10 '15 at 17:12

1 Answers1

1

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.

sulDu
  • 21
  • 7