CODEWITHSUNDEEP
google-apps-script
user2087338
user2087338

Reputation: 3

Insert Comment - GoogleSpreadsheets

I'm looking to insert comments onto a certain cell once a script has run. The script I am using is to insert a calendar entry - however I only want it to do this once. To do this, I will get the script to check if a comment/note is entered onto the cell. In theory, this should be easy but I'm having trouble making it select the right cell... Can anyone help? My code is below.

I'm after the comment to go in the cell that corresponds with var = date. If anyone can give me pointers, I'd really appreciate!

Cheers, Pete

    function CalendarInputIVNShortlist() {`

  `var sheet = SpreadsheetApp.getActiveSheet();

   var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 26); //What data will be used
  var data = dataRange.getValues();
  var cal = CalendarApp.getDefaultCalendar(); //Gets the correct calendar

    for (i in data) {
      var row = data[i];
      var title = row[1];  // Column with Title
      var desc = row[15];       // Column with Next Steps
      var date = row[5];   //Column with Date
       var invite = row[2] //Column with PDC Chairs & IVN Owner
         var sent = row[12] //Check Sent
         sheet.getRange(data[i], 5).setNote(Sent)


    cal.createAllDayEvent(title, new Date(date), {description:desc,guests:invite});
var Comment = dataRange(date).setNote("Inputted")

Upvotes: 0

Views: 2326

Answers (2)

Mogsdad
Mogsdad

Reputation: 45720

There's a problem here:

sheet.getRange(data[i], 5)

The first parameter you've used, data[i], is an array (that you've already equated to row a few lines earlier). The getRange() method variance you are looking for expects two Numbers as parameters. If your intent was to add a note to column 5 of the row currently being references in your loop, then you want:

sheet.getRange(i+startRow, 5).setNote(sent);

Another issue with that particular line is with Sent; I assume you wanted to reference the variable you has set equal to row[12], just above, which had a small letter 's' in its name.

Next, there's something wrong here:

var Comment = dataRange(date).setNote("Inputted")

What is dataRange()? Unless it's another function in your script, that returns a Range object relevant to the contents of row[5], I would guess you intended this:

sheet.getRange(i+startRow, 5).setNote("Inputted");

Upvotes: 1

Serge insas
Serge insas

Reputation: 46792

sheet.getRange() needs 2 integers as parameters : the row number and the column number.

In your code you are trying to assign an array (data[i]) as row nuber...this cannot work.

Since it counts from startRow and since array are 0 indexed I would suggest you try like that :

sheet.getRange(startRow+i, 5).setNote(Sent)

Upvotes: 0

Related Questions