1

I have a custom GAS function that would return 3 string values for each call. Each 3 values will be pushed into an array. So, I have the below values in an array after calling the function for 3 times:-

ArrValues=[["String1","Pattern1","Mesh1"],["String2","Pattern2","Mesh2"],["String3","Pattern3","Mesh3"]];

I want all these values to be listed in gsheet accordingly like below:-

expected output

At the moment, I coded as below to get the expected output:-

sheet.getRange(2,4,ArrValues.length,1).setValues(ArrValues.map(ArrValues=>[ArrValues[0]]));
sheet.getRange(2,5,ArrValues.length,1).setValues(ArrValues.map(ArrValues=>[ArrValues[1]]));
sheet.getRange(2,6,ArrValues.length,1).setValues(ArrValues.map(ArrValues=>[ArrValues[2]]));

I also can achieve the same using "for..." loop like below:-

for(i=0;i<ArrValues.length;i++){
    sheet.getRange(2,i+4,ArrValues.length,1).setValues(ArrValues.map(ArrValues=>[ArrValues[i]]));}

I would like to know is there any simpler as 1 line code which only will go through the elements once and return all the values in it's own individual column simultaneously, please? I tried below but it doesn't work.

sheet.getRange(2,4,ArrValues.length,3).setValues(ArrValues.map(ArrValues=>[ArrValues]));

dell
  • 171
  • 13
  • Does your code work properly? – TheMaster Dec 07 '20 at 08:36
  • yes, just that if possible want to have a simpler code that need not to run thru multiple times in the same array to return the values at different index. I'm thinking if there's a simpler way, it could save time since I need to do it for thousands of values and in future may involve more than 3 columns of values. – dell Dec 07 '20 at 08:46
  • I don't think your code works or your sample `ArrValues=["String1","Pattern1","Mesh1","String2","Pattern2","Mesh2","String3","Pattern3","Mesh3"];` is wrong – TheMaster Dec 07 '20 at 09:48
  • How is this `ArrValues` defined? manually by you? or you get it from somewhere else? – Marios Dec 07 '20 at 10:43
  • Very sorry @TheMaster. It didn't work earlier because there's no symbol '[' and ']' that groups each 3 elements being returned. I have just added them inside the question after I tested it just now. Earlier on I didn't add them because that's what I saw in the logs. – dell Dec 10 '20 at 12:18
  • Very sorry @Marios . ArrValues elements are the output being returned from other custom fn. I didn't put the first custom fn because I just wanted to point out my question is on the other fn that processes the codes to display them on the gsheet. Sorry for all the trouble. – dell Dec 10 '20 at 12:18

2 Answers2

2

Solution / Explanation:

You can use this approach to convert ArrValues into a 2D array with a shape of 3x3:

const ArrValues=["String1","Pattern1","Mesh1","String2","Pattern2","Mesh2","String3","Pattern3","Mesh3"];
const newArr = [];
while(ArrValues.length) newArr.push(ArrValues.splice(0,3));

and then paste them to the sheet with one line of code:

sheet.getRange(2,1,newArr.length,newArr[0].length).setValues(newArr);

If you have more columns/data, then change the argument 3 in the splice function. The latter defines the number of columns for which you want to split the data into. For example, if ArrValues consists of 10 elements and you want to create an array of 2 columns and 5 rows (2x5=10) then use splice(0,2) because you want 2 columns. Similarly, if you want 2 rows of 5 elements each, then use splice(0,5).

Result:

const ArrValues=["String1","Pattern1","Mesh1","String2","Pattern2","Mesh2","String3","Pattern3","Mesh3"];
const newArr = [];
while(ArrValues.length) newArr.push(ArrValues.splice(0,3));
console.log(newArr);
Marios
  • 26,333
  • 8
  • 32
  • 52
  • I would say this is really the solution I can use if the elements in the array were returned without the square brackets (which I didn't mentioned it earlier & it's my fault..sorry). – dell Dec 10 '20 at 12:40
  • 1
    I can use this if I can have one line of code prior to this that will eliminate that square brackets first. But I'm still trying to figure out that code because I've never thought to eliminate as I learnt earlier was how to add them instead. But your code could really be useful in future setups. Thanks a lot. – dell Dec 10 '20 at 12:41
1

You can plainly setValues the 2D array.

sheet.getRange(2,4,ArrValues.length,3).setValues(ArrValues)

See:

What does the range method getValues() return and setValues() accept?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Gosh! It was just that simple??! Never thought that the solution is just not to map it and just setValues as the array name.. Thanks a lot @TheMaster ! – dell Dec 11 '20 at 07:37