CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Dennis Tagamolila
Dennis Tagamolila

Reputation: 3

Google Apps Script is Not Detecting the Current Row

I created a function that's tied to a custom menu item in a google spreadsheet and is meant to detect the current row and perform some checking. The issue is that every time i run the menu item > script it always detect row 1 and not the current script. here's the code for reference:

function updateCalendarItems(){


//this function detects the current row and updates the main and follow up calendar events
  //Get the current row
  var ss = SpreadsheetApp.openById('<deleted>');
  var sheet = ss.getSheetByName('db_clientvisit');

  Logger.log('Sheet Name: '+sheet.getName()); //this logs the correct sheet name
//  var range = sheet.getActiveCell();
  var rowNum = sheet.getActiveCell().getRow();
  Logger.log(rowNum); //always logs 1
  var range = sheet.getRange(rowNum, 1, 1, 13).getValues(); //always returns the first row
  Logger.log(range);
  var ui = SpreadsheetApp.getUi();
  ui.alert('Row Num is :'+rowNum+' Column Num is: '+sheet.getActiveCell().getColumn());
  return;
  var range = sheet.getRange(rowNum, 1, 1, 13);
  var row = range.getValues()[0];
  Logger.log(row);
  //open the calendar 
  var calendarID = 'loveyourself.ph_umvj5k6vo45ei0mbh423g97ebg@group.calendar.google.com';
  var calendar = CalendarApp.getCalendarById(calendarID);
  //update the calendar...
}

Upvotes: 0

Views: 140

Answers (1)

Diego
Diego

Reputation: 9571

Use var ss = SpreadsheetApp.getActive().

When you use SpreadsheetApp.openById('<deleted>'), you're essentially opening up a new session of the spreadsheet instead of your active session. So Apps Script isn't able to get the "active" ranges.

Similarly, you may also want to try using SpreadsheetApp.getCurrentCell().

function updateCalendarItems() {
  var currentCell = SpreadsheetApp.getCurrentCell();
  var rowNum = currentCell.getRow();
  var ui = SpreadsheetApp.getUi();
  ui.alert('Row Num is :'+rowNum+' Column Num is: '+currentCell.getColumn());
  return;
}

Upvotes: 2

Related Questions