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

224 views
Skip to first unread message

Lorenzo

unread,
Oct 21, 2020, 3:18:36 AM10/21/20
to Google Apps Script Community
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

unread,
Oct 21, 2020, 5:41:50 AM10/21/20
to Google Apps Script Community

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

unread,
Oct 21, 2020, 6:12:08 AM10/21/20
to google-apps-sc...@googlegroups.com
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

unread,
Oct 21, 2020, 6:27:18 AM10/21/20
to Google Apps Script Community
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

unread,
Oct 21, 2020, 7:54:31 AM10/21/20
to Google Apps Script Community
the last sheet must be "Recap" and that there is data in A1 in all the sheets  

Elisa B

unread,
Oct 21, 2020, 3:53:02 PM10/21/20
to Google Apps Script Community
@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

unread,
Oct 21, 2020, 4:07:40 PM10/21/20
to google-apps-sc...@googlegroups.com
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

unread,
Oct 22, 2020, 4:04:15 AM10/22/20
to google-apps-sc...@googlegroups.com

@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

unread,
Oct 22, 2020, 5:39:40 AM10/22/20
to Google Apps Script Community
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

unread,
Oct 22, 2020, 5:47:39 AM10/22/20
to Google Apps Script Community
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

unread,
Oct 22, 2020, 5:57:54 AM10/22/20
to Google Apps Script Community
consolidation1.gif

Lorenzo

unread,
Oct 22, 2020, 6:09:46 AM10/22/20
to google-apps-sc...@googlegroups.com
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.


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

Lorenzo

unread,
Oct 22, 2020, 6:11:07 AM10/22/20
to google-apps-sc...@googlegroups.com
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.

Elisa B

unread,
Oct 22, 2020, 7:20:37 PM10/22/20
to Google Apps Script Community
@Gilbert,
oui, j'ai vu mon erreur, s range tous les noms des feuilles du tableau   :)
Elisa
Reply all
Reply to author
Forward
0 new messages