Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

google apps script - How to declare a variable range to a spreadsheet?

I can't set a variable to return the number to the last row under the K column in google sheets. The code fails on the last line .setValues(exampleText) where I have already defined exampleText as a variable.

I have used different methods of setting variables including setting a variable for a column.

I get the following error:

"Cannot find method getRange(number,number)."

The top three rows are an example I tried before.

Column = 'K' 
range = ss.getRange(LastRow, Column)
range.setValue(exampleText)

var ss = SpreadsheetApp.getActiveSpreadsheet()

var column = 11

var lastRow = ss.getLastRow();

ss.getRange(lastRow, column)
.setValues(exampleText)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Issue:

  • getRange is a method that's available both on Spreadsheet class and Sheet class.

  • However, only getRange(string) is available on Spreadsheet(ss). So, you can use ss.getRange('Sheet1!A1:B4').

  • And getRange(number,number) is only available on sheet. So, You can use ss.getActiveSheet().getRange(1,4).

  • Sheet class also accepts other variations of this method, but Spreadsheet class doesn't, which only accepts string as the only parameter.

  • Sometimes, You also receive

    Exception: The parameters (String,number,number,number) don't match the method signature for SpreadsheetApp.Spreadsheet.getRange

    The reason the first parameter is a string is because there is a attempt by JavaScript engine to match the actual function call signature: getRange(string). It converts the first number to string, but it doesn't know what to do with the rest of the parameters(numbers). As there is no method signature that match the call (string, number,number,number) on Spreadsheet class, it throws a error.

Solution:

Use proper methods on the intended class as described in the official documentation. For most purposes, You must use the Sheet class.

SpreadsheetApp.getActive().getSheets()[0].getRange(1,1)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...