CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsscriptingcustom-function
Jane Mänd
Jane Mänd

Reputation: 187

How to convert range to string in Google Spreadsheet?

I have a script in Google Spreadsheet that counts how many cells of specified colors are in the range (for example 10 cells in a range and 3 of them have a red font).

function countTextColor(inputRangeStr, inputColorStr) {
  var inputRange = SpreadsheetApp.getActive()
    .getActiveSheet()
    .getRange(inputRangeStr);
    . . . 
    . . . 
}

If I call the function where the range is as a string, then there's no problem:

=COUNTTEXTCOLOR("A1:F1", "#ff0000")

But I need to use the range as a real reference where it dynamically points to the specified cells. When calling it like this...

=COUNTTEXTCOLOR(A1:F1, "#ff0000")

... it returns Exception:

Range not found (line 9).

I understand that the reference needs to be converted to a string format but how to do that?

Upvotes: 2

Views: 5646

Answers (3)

Ashish Yadav
Ashish Yadav

Reputation: 101

This method does not require any script. Assuming the result of the formula is on the same row (eg. G1), the following formula will do the job and is extendable to rows below:

=COUNTTEXTCOLOR(CONCATENATE(ADDRESS(ROW(),1,4),":",ADDRESS(ROW(),6,4)),"#ff0000")

"1" and "6" in ADDRESS function above point to Columns A and F respectively. Changing these values or using COLUMN() function here can be utilized gainfully as per need.

Upvotes: 0

Marian
Marian

Reputation: 37

A workaround is to pass two parameters for the range. one as range like A1:B2 just to trigger function and one as string like "A1:B2" which will be actually used inside function.

So function call will look like this

=myFunction(A1:B2, "A1:B2")

And function declaration:

function myFunction(range, rangeStr) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cells = sheet.getRange(rangeStr);

  // do your job here
}

Upvotes: 0

ziganotschka
ziganotschka

Reputation: 26796

  • In Apps Script any value that is not strictly numeric must be a string
  • A string cannot be passed without quotes

As a workaround:

  • Save the real references without quotes in a separate column (e.g. column I)
  • Call you Apps Script with =COUNTTEXTCOLOR(TO_TEXT(I1), "#ff0000") - this will convert your range notation to a string

Upvotes: 0

Related Questions