1

I'm fairly new to AppScript, so I apologise if this is crazy easy and I'm just hopeless. I've searched a bunch, but solutions were all in slightly different formats that I wasn't sure how to utilise.

The script works to import my sheets information into the calendar, but I want my first column to be a formula column and I can't figure out how to ignore it while using LastRow.

I got the code from a tutorial online, but if you all have suggestions on what to do better/differently, let me know. Thank you!

I've tried a few different iterations in this line:

var count = spreadsheet.getRange("B2:H"+lr+"").getValues();

But I it's been trial and error, with only errors being the result.

function CreateEvent() {

var spreadsheet = SpreadsheetApp.getActiveSheet();
var calendarId = spreadsheet.getRange('K1').getValue();
var eventCal = CalendarApp.getCalendarById(calendarId);
var lr = spreadsheet.getLastRow();
Logger.log(lr);

var count = spreadsheet.getRange("B2:H"+lr+"").getValues(); 

for (x=0; x<count.length; x++) {

  var shift = count[x];

  var summary = shift[0];
  var guests = shift[2]; 
  var description = shift[3];
  var location = shift[4];
  var startTime = shift[5];
  var endTime = shift[6];
  var event = {
      'location': location,
      'description': description,
      'guests':guests +',',
  }

  eventCal.createEvent(summary, startTime, endTime, event)
}


}
N3R4ZZuRR0
  • 2,400
  • 4
  • 18
  • 32
Chanceofrust
  • 11
  • 1
  • Possible duplicate of [ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?](https://stackoverflow.com/questions/46883862/arrayformula-is-breaking-the-getlastrow-funtion-possible-workarounds) – TheMaster Sep 21 '19 at 07:35
  • 1
    Welcome. 1) What is your value for "lr"? 2) You said "first column to be a formula column and I can't figure out how to ignore it while using LastRow." I'm just confused how the first column is related to the last row. Would you explain? 3) "with only errors being the result." That's too vague. Would you please copy/paste the Execution transcript into your question so we can understand the exact error. – Tedinoz Sep 21 '19 at 11:31
  • Sorry for not explaining myself well enough, thank you for taking the time to reply. I want to have the first column sitting with formulas in it all the time, but when I run the script with that first column filled but all the others empty, it errors out. The script I borrowed doesn't have a value for lr, which is why I'm having trouble knowing how to fill it in. I don't have my computer with me to give you the errors right now, I'm sorry. Just wanted to give you a reply, but will update later. – Chanceofrust Sep 21 '19 at 23:25
  • So if I try to run it with my Column A having the functions all the way down, I get the error "Cannot find method createEvent(string,string,string,object). (line 27, file "macros")" I've tried adjusting the range in `var count = spreadsheet.getRange("B2:H"+lr+"").getValues();` But I clearly am not getting it right, as I mostly get "Range not found (line 9, file "macros")" or "missing ) after argument list" – Chanceofrust Sep 22 '19 at 11:07
  • I suspect that you are executing the script while the active sheet is NOT the sheet containing the value in K1. Suggest you delete line 2 ("var spreadsheet = SpreadsheetApp.getActiveSheet();") and substitute the following to ensure that the code will always run against the data in "Sheet1" regardless of the current location of the cursor: `var ss = SpreadsheetApp.getActiveSpreadsheet();`, `var sheetname = "Sheet1";` (obviously edit for your actual sheet name), and `var spreadsheet = ss.getSheetByName(sheetname);`. Then run the code and report back with any errors. – Tedinoz Sep 22 '19 at 13:38

1 Answers1

0

Your approach is right, there is only one step that you need to take in order to create the event. The startTime and endTime parameters have to be Date type, no String. To achieve that you need to add the following changes:

var startTime = new Date(shift[5]);
var endTime = new Date(shift[6]);

With this code the strings will be converted into dates and the createEvent method will work. Please, ask for more clarification if I haven't explained myself adequately.

As a sidenote I want to commet one little thing, in the following line:

var count = spreadsheet.getRange("B2:H"+lr+"").getValues();

The last "" are not needed at all and may cause confusion, but it does certainly work. For clarity, I recommend just using:

var count = spreadsheet.getRange("B2:H"+lr).getValues();
Jacques-Guzel Heron
  • 2,480
  • 1
  • 7
  • 16