I am trying to return the row of the active cell in google sheets so I can use it in another the formula of another cell. I have found several simple scripts that supposedly do this but have not been able to get any of the to work. I apologize in advance for my lack of understanding in the scripting environment. I do very little programming and my past experience was 25+ years ago (not much has changed since then, right???). Anyway, any help that you can give me would e greatly appreciated. I have found this:

function GetActiveRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var range = s.getActiveRange();
  var row = range.getRow(); 
  var values = s.getRange( row + ':' + row ).getValues();  
  s.getRange('A1').offset(0, 0, 1, values[0].length).setValues(values);
}

But when I enter that in as a script and try the formula =GetActiveRow() it returns an error that states:

Error You do not have permission to call setValues (line 7)

I probably shouldn't be playing in the scripting area without a better understanding of it but this is the only simple script I need. Any help is greatly appreciated.

I am trying to return the row of the active cell in google sheets so I can use it in another the formula of another cell. I have found several simple scripts that supposedly do this but have not been able to get any of the to work. I apologize in advance for my lack of understanding in the scripting environment. I do very little programming and my past experience was 25+ years ago (not much has changed since then, right???). Anyway, any help that you can give me would e greatly appreciated. I have found this:

function GetActiveRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var range = s.getActiveRange();
  var row = range.getRow(); 
  var values = s.getRange( row + ':' + row ).getValues();  
  s.getRange('A1').offset(0, 0, 1, values[0].length).setValues(values);
}

But when I enter that in as a script and try the formula =GetActiveRow() it returns an error that states:

Error You do not have permission to call setValues (line 7)

I probably shouldn't be playing in the scripting area without a better understanding of it but this is the only simple script I need. Any help is greatly appreciated.

• javascript
• google-sheets-api

Share Improve this question Follow edited May 24, 2018 at 7:18 Pro Q 5,08855 gold badges5656 silver badges108108 bronze badges asked May 23, 2018 at 23:52 SpurzSpurz 2111 silver badge33 bronze badges 14

• Hi Spurz! Wele to Stack Overflow :) What was the error that you got when you typed that in? – Pro Q Commented May 23, 2018 at 23:59
• Error You do not have permission to call setValues (line 7). – Spurz Commented May 24, 2018 at 0:03
• Okay! If you add that error message into your answer, it may help people who see this question to help you. I have enough reputation on this site that they asked me to help set you up since you're a new user, but unfortunately I don't have enough experience with Google Sheets to answer your question :( If you do have more questions on how to improve your question or how the site works, feel free to ment below and I'll try to help. – Pro Q Commented May 24, 2018 at 0:07
• I may be trying to cram an ould-school thought process into a place where there is a simpler solution. What I am hoping to do is return the row number of the active cell as it moves around (i.e. using a function in cell a1 that tells me the row of the active cell as it moves around. – Spurz Commented May 24, 2018 at 0:10
• 1 I edited my original post to include the error message. – Spurz Commented May 24, 2018 at 0:19

 |  Show 9 more ments

1 Answer 1

Sorted by: Reset to default 3

Here's a sample code for you.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var getActiveRow = [{name: "getActiveRow", functionName: "fetchActiveRow"}];
     ss.addMenu("customMenu", getActiveRow);
};

function fetchActiveRow(){ 
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var row = ss.getActiveSheet().getActiveRange().getRow();

     var ui = SpreadsheetApp.getUi();
     var response = ui.alert("active row is " + row);
}

This creates a custom menu button on your spreadsheet named customMenu which contains getActiveRow button. When clicked, it will prompt you of the current active row in the sheet. Try it and modify as you please.