help google sheet script - retrieving a value from each sheet of a spreadsheet

[Lorenzo]

Hi,

I have a big spreadsheet with a lot of sheets and I need to summarize some results in the last sheet. I need a script that can get the value of a cell (for example A1) from each sheets and then write them down.

the output that I need is such a array:

sheet1cellA1value

sheet2cellA1value

sheet3cellA1value

...

Can someone help me please?

Kind Regards

[gilbert_RGI]

hello Lorenzo; 

Something like this the last sheet being 

the summary sheet

the repatriated cell is A1

function recapA1(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Récap");

var s = ss.getSheets();

var nSheets =  ss.getNumSheets();

for (var i=0;i<nSheets;i++){

  if (i===nSheets-1){}else{

    spreadsheet.getRange(i+1,1).setValue(ss.getSheetByName(s[i].getName()).getRange("A1").getValue());

  }  } 

}

Greetings  

Gilbert

[Darren D'Mello]

Why not use importrange formula?

--
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/4e4572c8-e722-4d0a-81e4-efe540abb8e4n%40googlegroups.com.

[Lorenzo]

Hi Gilbert,

thank you for the help!

unfortunately I get this error: TypeError: Cannot read property 'getRange' of null (line 8).

I tried with a new spreadsheet with only 2 sheets with some random values in the A1 cell too.

[gilbert_RGI]

the last sheet must be "Recap" and that there is data in A1 in all the sheets  

[Elisa B]

@Gilbert,

Est-ce que cela ne viendrait-il pas de la ligne

  var s = ss.getSheets();  

au lieu de 

  var ss = ss.getSheets();

Elisa

[RGI Gilbert]

Non chez moi ça fonctionne 

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/Nb_4IlhQFWM/unsubscribe.
To unsubscribe from this group and all its topics, 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/d8bc6132-d347-4cc3-ac62-ed0add3e662bo%40googlegroups.com.

[gilbert_RGI]

@Lorenzo

to avoid the error of the missing data in A1 in the sheets to be retrieved I improved the code  

function recapA1(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Récap");

var s = ss.getSheets();

var nSheets =  ss.getNumSheets();

var a = 0

for (var i=0;i<nSheets;i++){

  if (ss.getSheetByName(s[i].getName()).getRange("A1").getValue()==""){a++;}else{

  if (i===nSheets-1){}else{

    spreadsheet.getRange((i+1)-a,1).setValue(ss.getSheetByName(s[i].getName()).getRange("A1").getValue());

  }  } } }

@ Elisa non surtout pas ;-)))

[Lorenzo]

I still getting an error :

Exception: You do not have permission to call setValue (line 10).

my last sheet in the spreadsheet is called "Récap"

all the A1 cell have a random value

Unfortunately I don't know the google script code and I don't have time to learn it.  :S Thank you for your help

[gilbert_RGI]

are you the owner of the file ???? if not you need the owner's modification permissions 

have you given permissions to the scripts to run ? 

[gilbert_RGI]

[Lorenzo]

Thank you for the video.

I was calling the function inserting in a cell =recapA1() instead running the script. That's why it didn't work.

Now it works fine.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/Nb_4IlhQFWM/unsubscribe.
To unsubscribe from this group and all its topics, 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/d0b6d772-186d-4eed-8709-4b1398d8fd5en%40googlegroups.com.

--

Ing. Lorenzo Rossini
-------------------------------------------------------
Mobile (I): +39 333 3107773
Mobile (CH) : +41 (0)76 7009024
Mail: lorenzo...@gmail.com
-------------------------------------------------------

[Lorenzo]

Thank you for the video.

I was inserting the function =recapA1() in a cell instead of running the script. That's why it didn't work!

Il giorno gio 22 ott 2020 alle ore 11:57 gilbert_RGI <gilbe...@gmail.com> ha scritto:

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/Nb_4IlhQFWM/unsubscribe.
To unsubscribe from this group and all its topics, 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/d0b6d772-186d-4eed-8709-4b1398d8fd5en%40googlegroups.com.

[Elisa B]

@Gilbert,

oui, j'ai vu mon erreur, s range tous les noms des feuilles du tableau   :)

Elisa