Google Sheet Apps Script: Store and retrieve values from a for loop

[Julien PANEVEL]

Hi there,

I'm trying to make a form on Sheets that will create a table for me based on the answers.

My problem is that I can't get responses from a loop.

I created the following script:

function params() { 

var ui = SpreadsheetApp.getUi();

var result = ui.prompt( 

"GENERAL", 

'Enter number of pools',

ui.ButtonSet.OK_CANCEL);

var button = result.getSelectedButton();

var text = result.getResponseText();

if (button == ui.Button.OK)

{

for (i = 1; i <= text; i++) 

{

var result = ui.prompt( 

  "GENERAL", 

  'Pool name n°'+i + ' ?',

ui.ButtonSet.OK_CANCEL)

var poolsNames = result.getResponseText();

}

SpreadsheetApp.getUi().alert('names : ' + poolsNames); } }

In this example, after asking for the names of the basins, I cannot retrieve its different values to put them on a UI Alert for example.

This gives me only the last value instead of all pool names.

Well, I don't know if it's understandable, I'm available if it's not clear.

Good for you.

[cbmserv...@gmail.com]

If you have a loop collecting information and then you want to see all the info collected after, it is best to save the info in an array so that you can go through them one by one after.

 

So do the following:

 

Declare your variable outside the loop as follows:

 

Var poolsNames = [];   // empty array

 

Inside the loop, do the following:

 

poolsNames.push(result.getResponseText());

 

 

After the loop, you should have all the values saved in the array and you can check the length of the array for how many you have as follows:  poolsNames.length  Hope this helps.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/810a657b-0389-4eef-b862-472386f2133en%40googlegroups.com.

[Julien PANEVEL]

Thanks you very much !

It's working perfectly

I had taken the "ui.alert" to serve as an example.

I wanted to transfer the pools names to cell A1, then B1 ...

i tried : 

function params()

{ var sheet = SpreadsheetApp.getActive().getSheetByName('PLANNING');

var ui = SpreadsheetApp.getUi();

var result = ui.prompt( 

"GENERAL",

'Enter number of pools',

ui.ButtonSet.OK_CANCEL);

var button = result.getSelectedButton();

var text = result.getResponseText();

var poolsNames = [ ];

if (button == ui.Button.OK)

{

for (i = 1; i <= text; i++)

{

var result = ui.prompt( 

"CONFIGURATION GÉNÉRALE",

'Pool name n°'+i + ' ?',

ui.ButtonSet.OK_CANCEL);

poolsNames.push(result.getResponseText());

}

for (i=0; i < poolsNames.length; i++); 

{

var PoolsRange = sheet.getRange(1,i);

PoolsRange.setValue(nomsBassins);

} 

} 

}

There is an error :  Cannot read property 'getRange' of null.

[CBMServices Web]

Your error is because of how you are defining the variable sheet.

Change it to: 

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(...

The getActive method returns the sheet, what you want is to return the spreadsheet and then select a particular sheet in it.

Also your last for loop starts i at value 0. There is no column 0, so make sure to do getRange(1, i+1).setValue..

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b9c139e2-1b8c-4f09-942a-0d5afc88df67n%40googlegroups.com.

[Julien PANEVEL]

Thank you again George !

I have just the first pool name in 'C1'.

So i tried this :

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PLANNING DES ACTIVITES');

and :

for (i=0; i<nomsBassins.length; i++);

{

var col = parseInt(i)+1;

var poolsRange = sheet.getRange(1,col);

poolsRange.setValue(nomsBassins[i]);

}

and....nothing happens :/

[CBMServices Web]

You really don't need a for loop to write the values back to the spreadsheet from an array.

Try this instead of whole for loop.

sheet.getRange(1,1,1,nomsBassins.length).setValues([nomsBassins]);

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1d54d0eb-4708-4b4d-81f1-c1b31d2a6762n%40googlegroups.com.

[Julien PANEVEL]

Waow, it's working !

Nice done, thank you George ;)

[cbmserv...@gmail.com]

You are welcome.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/23475988-16b1-4b04-affc-eea5130c1a81n%40googlegroups.com.

[Julien PANEVEL]

Just a last question George,

After that I think I will have everything I need to allow me to do my project.

if I want to display the basin names in a column: A1 then A2 then A3 ?

I tried : 

sheet.getRange(1,1,nomsBassins.length,1).setValues([nomsBassins]);

it returned :  Exception: The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 3.

A last try ?

[cbmserv...@gmail.com]

The array right now is just 1 dimensional  like this:  [ item1, item2, item3 ]

 

setValues expects a 2 dimensional array being given to it. So it wants this:

 

[  [ item1, item2, item3 ],    [item4, item5, item6] … ]   each internal array is written to one row, then the next and so on.

 

One way to solve this is to change how you build your array.

 

In the for loop where you collect the items and push in the next value into the array, push it in as an array (as one row).

 

This will allow you to do as you require.

 

So here in this loop:

 

for (i=0; i < poolsNames.length; i++); 

{

var PoolsRange = sheet.getRange(1,i);

PoolsRange.setValue([nomsBassins]);  // add square brackets around nomsBassins, so it gets pushed in as an array.

} 

Now it should work.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6708f829-9314-46a5-afda-6d7b6a7d3883n%40googlegroups.com.

[Julien PANEVEL]

Thanks again !

It returned only the first name in C1 :)

i put all the code below :

function parametrage()

{

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PLANNING DES ACTIVITES');

  

  var ui = SpreadsheetApp.getUi();

  var result = ui.prompt(

    "CONFIGURATION GÉNÉRALE",

    'Entrer le nombre de bassins',

    ui.ButtonSet.OK_CANCEL);

  var button = result.getSelectedButton();

  var text = result.getResponseText();

  var nomsBassins = [];

  if (button == ui.Button.OK)

{

for (i = 1; i <= text; i++)

{

  var result = ui.prompt(

    "CONFIGURATION GÉNÉRALE",

    'Nom du bassin n°'+i + ' ?',

    ui.ButtonSet.OK_CANCEL);

    nomsBassins.push(result.getResponseText());

}

for (i=0; i < nomsBassins.length; i++); 

{

var bassinsRange = sheet.getRange(1,i);

bassinsRange.setValue([nomsBassins]);

} 

}

}

i tried also : bassinsRange.setValue([nomsBassins]);

=> Exception: The number of columns in the data does not match the number of columns in the range. The data has 3 but the range has 1.

[Julien PANEVEL]

hum sorry : correction :

i tried also : bassinsRange.setValues([nomsBassins]);

=> Exception: The number of columns in the data does not match the number of columns in the range. The data has 3 but the range has 1.

[cbmserv...@gmail.com]

Julien,

 

Here is the whole function. Just copy paste it in. I tested it to make sure its working for you.

 

function parametrage()

{

 

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PLANNING DES ACTIVITES');

  var ui = SpreadsheetApp.getUi();

  var result = ui.prompt(

    "CONFIGURATION GÉNÉRALE",

    'Entrer le nombre de bassins',

    ui.ButtonSet.OK_CANCEL);

 

  var button = result.getSelectedButton();

  var text = result.getResponseText();

  var nomsBassins = [];

 

  if (button == ui.Button.OK)

 

  for (i = 1; i <= text; i++)

  {

    var result = ui.prompt(

      "CONFIGURATION GÉNÉRALE",

      'Nom du bassin n°'+i + ' ?',

      ui.ButtonSet.OK_CANCEL);

 

      nomsBassins.push([result.getResponseText()]);

  }

  sheet.getRange(1,1,nomsBassins.length,1).setValues(nomsBassins);

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4c01dcfd-0aad-4be1-9981-f3dc24f2ff15n%40googlegroups.com.

[Julien PANEVEL]

Nice ! Thank you so much !

[Julien PANEVEL]

Georges,

Sorry to disturb you again  !

Can you help me again to put a loop in a loop: 

My pools have multiple spaces. 

In order to create a schedule of activities, i would like to name these spaces in the different swimming pools like the diagram below :

After that, i would like to put the data collected as in the example below :

I tried :

function parametrage()

{

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PLANNING DES ACTIVITES');

 var ui = SpreadsheetApp.getUi();

 var result = ui.prompt

 (

    "CONFIGURATION BASSINS",

    'Entrer le nombre de bassins',

    ui.ButtonSet.OK_CANCEL

 );

 var button = result.getSelectedButton();

 var nombreDeBassins = result.getResponseText();

 var nomsBassins = [];

 var nombreEspaces = [];

 var nomsEspaces = [];

 if (button == ui.Button.OK)

 {

   for (i = 1; i <= nombreDeBassins; i++)

   {

      var result = ui.prompt

      (

        "CONFIGURATION BASSINS",

        'Nom du bassin n°' + i + ' ?',

        ui.ButtonSet.OK_CANCEL

      );

      nomsBassins.push([result.getResponseText()]);

   };

    for (i = 0; i < nombreDeBassins; i++)

    {

      var result = ui.prompt

      (

        "CONFIGURATION BASSINS",

        'Combien d\'espaces a le bassin ' + nomsBassins[i] + ' ?',

        ui.ButtonSet.OK_CANCEL

      );

      

      nombreEspaces.push([result.getResponseText()]);

    };

    for (i = 1; i < nombreDeBassins; i++)

    {

      var result = ui.prompt

      (

        "CONFIGURATION BASSINS",

        'Nom de l\'espace '+ j + 'du bassin ' + nomsBassins[i] + ' ?',

        ui.ButtonSet.OK_CANCEL

      );

      nomsEspaces.push([result.getResponseText()]);

    }

    sheet.getRange(3,2,nomsBassins.length,1).setValues(nomsBassins);

  }

}

I think, i have to put a loop in the loop...

[cbmserv...@gmail.com]

Hi Julien,

 

Yes, would be happy to help. Perhaps it is best to take this offline from the group discussion so that we don’t fill the discussion group with back and forth comments.

 

I sent you a private email. Please respond to that and we can continue.

 

From: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com> On Behalf Of Julien PANEVEL
Sent: September 14, 2022 3:14 PM
To: Google Apps Script Community <google-apps-sc...@googlegroups.com>
Subject: Re: [Apps-Script] Google Sheet Apps Script: Store and retrieve values from a for loop

 

Georges,

 

Sorry to disturb you again  !

 

Can you help me again to put a loop in a loop: 

 

My pools have multiple spaces. 

In order to create a schedule of activities, i would like to name these spaces in the different swimming pools like the diagram below :

 

 

After that, i would like to put the data collected as in the example below :

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f487377c-deda-4d2f-afee-b400ee9607c0n%40googlegroups.com.