Fetching data in range to process client-side (Google Sheets)

Question

Using Google Scripts I'm trying to return a string and I get an array.

The code is adapted from another answer: Find Cell Matching Value And Return Rownumber

function updateValues(sheet) 
{  // loads named range "CalendarSettings" (on Reserved tab) into array dataSearch for later searching
   dataRangeSearch = sheet.getRange("CalendarSettings");
   dataSearch = dataRangeSearch.getValues().reduce(function (a, b) {return a.concat(b);});;
}

I was expecting that dataSearch would be a string, since I thought that a.concat(b) would result in a string.

"CalendarSettings" is a named range. Here is a screenshot of what the named range contains.

And here's a screenshot of a msgBox showing the contents of the variable dataSearch.

This looks like an array to me, and it behaves like an array in terms of how it is accessed. But what confuses me is that a.concat(b) I thought would be a string.

Can you provide a sample values of `dataRangeSearch.getValues()` and the result you want? — Tanaike, Jul 27 '19 at 23:23
I don't think that there is a method sheet.getRange(string) unless the string is the A1Notation for a range. — Cooper, Jul 28 '19 at 00:53
If "CalendarSettings" is a named range then that would be `sheet.getRangeByName("CalendarSettings").getValues();` — Cooper, Jul 28 '19 at 00:59

score 0 · Answer 1 · answered Jul 29 '19 at 13:22

Strings and Arrays in JavaScript both have .concat() methods - using .concat() on two arrays will return an array, and on two strings will return a string.

You can turn an array into a string with .toString():

dataSearch = dataRangeSearch.getValues().reduce(function (a, b) {return a.concat(b).toString();});;

Fetching data in range to process client-side (Google Sheets)

1 Answers1