Cannot find method getRange(int,int) in Google Sheets

0
votes

When I change a getRange('D4') command to a getRange(i,4) or even getRange(4,4) I get the error:

Cannot find method getRange(number,number)

I am editing a Google Sheets macro function that intends to copy data from a known cell 'G6' to a given row (i) in column D (in this particular instance 'D4') based upon matching an index in column C to determine the row i.

I believe the for-loop correctly identifies the row correctly, but please tell me if there is an obvious issue with this.

spreadsheet.getRange(i,4).activate();

because it returns the titled error

Cannot find method getRange(number,number).

I also get this error if I try

spreadsheet.getRange(4,4).activate();

however, there is no issue when I run

spreadsheet.getRange('D4').activate();

which is where I began to record a macro.

function CopyMacro() {
  var spreadsheet = SpreadsheetApp.getActive();
  var data = spreadsheet.getDataRange().getValues();
  var playerID = spreadsheet.getRange("F3").getValue();
  var playerID2 = spreadsheet.getRange("F6").getValue();
  spreadsheet.getRange('I3').activate();
  spreadsheet.getRange('G3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    for(var i = 0; i<data.length;i++){
      if(data[i][2] == playerID){
        spreadsheet.getRange(i,4).activate();
        spreadsheet.getRange('G6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);        
      }
    }
1
google-apps-scriptgoogle-sheets

1 Answers

4
votes

How about this answer?

Modification points:

SpreadsheetApp.getActive() returns Spreadsheet. getRange(row, column) is the method of Class Sheet. When you want to use getRange method for Spreadsheet, please use getRange(a1Notation). So in your situation, how about modifying as follows?

Modified script:

Pattern 1:

In this pattern, getRange(a1Notation) is used.

spreadsheet.getRange(4,4).activate();

spreadsheet.getRange("D4").activate();
  • In this case, the 1st sheet is used. For example, if you want to use other sheets, please modify getRange("D4") to getRange("Sheet2!D4").

Pattern 2:

In this pattern, getRange(row, column) is used.

spreadsheet.getRange(4,4).activate();

var spreadsheet = SpreadsheetApp.getActive();
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(4,4).activate();
  • In this case, the active sheet is used. For example, if you want to use other sheets, please modify SpreadsheetApp.getActiveSheet() to spreadsheet.getSheetByName(name) and spreadsheet.getSheets()[i].

Pattern 3:

In this pattern, your bottom script in your question is modified. When above modification points are used for your bottom script, it becomes as follows. Please think of this as just one of several modifications.

spreadsheet.getRange(i,4).activate();

spreadsheet.getActiveSheet().getRange(i,4).activate();

References:

If I misunderstood your question and this was not the result you want, I apologize.