CODEWITHSUNDEEP
google-apps-script
Ghandi Manning
Ghandi Manning

Reputation: 123

dynamically generate range in google script

I am completely new to Google App Scripts and am struggling to dynamically generate a range.

I have created a button in the toolbar which calls a function. What I want the function to do is to set the active range based on the current cell but using the row only. So if the user is on ANY column in row 6 it will still only set the active range to ("A6:G6")

All I have so far is

myFunction() {
  var ss = SpreadsheetApp;
  var onRow = ss.getActiveRange().getRow();
  xl.setActiveRange(onRow, 1, 1, 7);
}

I am aware that last line is not how it works but I that's what I can't figure out. I want to pass in onRow as the variable with the following three figures fixed, which represent column number, row count and column count respectively.

Upvotes: 2

Views: 10798

Answers (1)

Ryan Roth
Ryan Roth

Reputation: 1424

When using Google Apps Script with Spreadsheets, you need to be careful to not confuse the SpreadsheetApp, Spreadsheet, Sheet and Range objects and their methods. In your sample, you are misusing the SpreadsheetApp object.

Here is some sample code that does what you intended, controlled by a simple menu item. Note that it isn't strictly necessary to create all of these variables in setRangeByRow(); this is just done here for clarity:

/**
 * Runs when the document is opened.
 * Sets up menu controls.
 */
function onOpen() {
  SpreadsheetApp.getUi().createMenu('Test Range Set')
    .addItem('Select Range', 'setRangeByRow')
    .addToUi();
}

/**
 *  Acquires the row of the current active cell
 *  and sets the active range to the first 7
 *  columns of that row.
 */
function setRangeByRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var onRow = sheet.getActiveRange().getRow();
  //Logger.log("ACTIVE ROW: "+onRow);
  var range = sheet.getRange(onRow, 1, 1, 7);
  sheet.setActiveRange(range);
}

Upvotes: 2

Related Questions