0

I have one thousand Google Form Responses spreadsheets. These are students answer sheets. I built a spreadsheet and pull data (TimeStamps and scores) for each student by using Google Spreadsheet formulas (INDEX MATCH and IMPORTDATA). Each student has different pages. But, it takes too many times and sometimes causes some source sheets being unresponsive (I think because of heavy formula usage). My questions;

  1. Is it possible to do the same thing (pulling data if matches student's name from one thousand spreadsheets) by using Google Script?
  2. If possible, which ones (Google Spreadsheets with formulas or Google Script) performance is better?

By looking your answers I will decide to begin learning Google Script or not.

Thanks in advance.

Rubén
  • 34,714
  • 9
  • 70
  • 166
hknBy
  • 1
  • 2
  • `IMPORTDATA` loads data from a url which can take more than 100ms. Loading 1000 urls can take 2 minutes or more. Could that be the problem here? – Tesseract Sep 14 '17 at 15:57
  • Could you give as an example of what the data and equations look like? – Tesseract Sep 14 '17 at 16:16
  • 'IFERROR(FILTER(IMPORTRANGE(E14;"'Form Yanıtları 1'!A1:B");IMPORTRANGE(E14;"'Form Yanıtları 1'!D1:D")=$G$1))' – hknBy Sep 14 '17 at 16:34
  • IFERROR(IF(G14="";"";(MATCH("*"&$G$1&"*";(IMPORTRANGE(E14;"'Form Yanıtları 1'!D1:D"));0)-1)&"/"&RANK(H14;IMPORTRANGE(E14;"'Form Yanıtları 1'!B1:B"))&"/"&linkHakan!M14);"") – hknBy Sep 14 '17 at 16:34
  • These two formulas works for 40 each student for 1000 row. G1 is students name and E14 is where sheets adresses are. – hknBy Sep 14 '17 at 16:35
  • I just did a little test. I took this formula `IFERROR(FILTER(IMPORTRANGE(E14;"'Form Yanıtları 1'!A1:B");IMPORTRANGE(E14;"'Form Yanıtları 1'!D1:D")=$G$1))` and translated it to Apps Script. Then I ran it on one single sheet with 1000 rows. It took 300ms in total. So if you were to run it on 1000 Spreadsheets it would probably take 300 seconds. But that is just a guess. – Tesseract Sep 15 '17 at 20:24

2 Answers2

0

Is it possible to do the same thing (pulling data if matches student's name from one thousand spreadsheets) by using Google Script?

Yes, it's possible.

NOTE: Bear in mind that Google Sheets has a 5 million cell limit, so if your data exceeds this limit, you should consider to use another data repository.

If possible, which ones (Google Spreadsheets with formulas or Google Script) performance is better?

Since most Google Sheets formulas are recalculated every time that a change is made in the spreadsheet that holds them, it's very likely that Google Apps Script will be better when using Google Sheets/Google Apps Script as database management system because we could have more control over when the database transactions will be made.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
-2

Both does the same thing. Both will be as intensive on your computer. My advice would be to upgrade your PC!

James Sheard
  • 139
  • 9
  • Google sheets and scripts are processed on google's servers. So it doesn't matter how fast you PC is. – Tesseract Sep 14 '17 at 16:15
  • @SpiderPig google sheets have a tendency to go slower on low spec machines. I use a lot of different machines and there's a clear difference between the top spec and low spec on google sheets and docs etc. – James Sheard Sep 14 '17 at 16:25
  • CPU Intel Core i5 (6th Gen) 6200U / 2.3 GHz, RAM 8 GB, DDR3L SDRAM 1600 MHz / PC3L-12800, Intel HD Graphics 520 and 256 GB SSD. I think not very low for a web app. – hknBy Sep 14 '17 at 17:01
  • Most built-in functions run locally other way spreadsheet can't work offline. Only functions like IMPORTRANGE and GOOGLEFINANCE and scripts are ran on the Google's servers. – Rubén Jan 03 '18 at 07:40