Google Apps Script – How to Get a Substring from a Cell in Google Sheets

google sheetsgoogle-apps-script

How do I get a substring from a cell in Google Apps Script?

I'm trying to retrieve some data from a website.
I've already got the whole HTML code
and put it into a cell (thou it could be better put into a String variable)

I'm stuck in a simpler task:
How do I get the part of that cell/string I'm interested in,
(since I know it is always located in the same position/offset)

I tried string.substring(offset, length)
but it doesn't work.

How can I do that?
Help appreciated.

Best Answer

The string.substring(offset, length) works for me.

Let me show you:

var alfabeto = "ABCDEFGHIJKLMNOPQRSTUVWXTZabcdefghiklmnopqrstuvwxyz";
var letra = "";
letra = alfabeto.substring(0,1);

Result: letra = A

Related Solutions

Google Sheets Query – How to Reference a Cell Containing a Date

Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string:

=JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6))))

You don't need transpose, because join works equally well with one-dimensional arrays of either direction: vertical or horizontal.

That said, I find a solution using filter slightly more readable:

=join(", ", unique(filter(E$2:E$4, A$2:A$4 = A6)))

Google Sheets – How to Use INDIRECT to Create Dynamic Length Range

Part 1

How can I use the function counta() as the end of each column's chosen range?

Use CONCATENATE, CONCAT or & operator.

As you already figured out, to create a dynamic reference use INDIRECT. Example:

=INDIRECT("S2:S"&COUNTA(S2:S))

Part 2

The final formula is

={INDIRECT("S2:S"&COUNTA(S2:S));INDIRECT("AI2:AI"&COUNTA(AI2:AI))}

An alternative to get the same result is to use FILTER. The advantage is that it will work for strings and numbers. Example:

={FILTER(S2:S,LEN(S2:S);FILTER(AI2:AI,LEN(AI2:AI)}

Another alternative is to use QUERY. The advantage of it is that the import formulas could be combined into an array an use it of the data argument of QUERY:

=QUERY({import1;import2},"select Col1 where Col1<>'' and Col1<>'Header'")