1

Using Google scripts, I'm creating a bunch of spreadsheets each of which contains an importrange function. In new google sheets one needs to allow access for that importrange to fetch data from a source file - I was wondering whether it was possible to make it automatic using Google scripts and get round the need to press "allow" manually each time?

Rubén
  • 34,714
  • 9
  • 70
  • 166
a-change
  • 656
  • 5
  • 12

2 Answers2

1

It is definitely possible to write an importrange function in Apps Script, but you'll need to run it too, so I don't know if it's going to be any better than clicking "allow".

I guess that, depending on your use-case, you could write a single script in the source sheet that pushes the data to the other sheets and possibly even create them automatically for you. Therefore saving you this click per sheet and possibly some other clicks. We can surely help you here in StackOverflow, but it's mostly working out problems, you'll have to put in some effort to develop it yourself. Take a look at Apps Script page to get started.

Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
  • Henrique, thank you for your reply. I thought this need to click "allow" was connected to authorization issues, as old sheets never asked about anything like this and importrange there would start immediately after being entered. That's why I thought it might be possible to get round this authorization directly from scripts. If it's about running the function, then ok. Actually, number of spreadsheets where this "allow" has to be clicked is quite reasonable in my case and automatic work would just make it a bit more comfortable. – a-change Jul 05 '14 at 23:24
  • As for writing a function I'm not so sure. As I understand, on edit triggers would have to be added to each file in this case and it would hardly be any faster than running importrange. Thank you again, any way :) – a-change Jul 05 '14 at 23:28
  • I'm not sure I understood you. But the solution I proposed, of having a single script in the source sheet, does not require a on-edit trigger on each file, that's the whole point of having a central script instead of distributed ones. But anyway, you know your use-case and what might work for you, that's probably why my suggestion does not make sense to you. – Henrique G. Abreu Jul 06 '14 at 00:57
  • Henrique, yes, I got you wrong here :) Sorry But anyway you would use a on-edit trigger in the source sheet in this case? – a-change Jul 06 '14 at 07:20
  • Yes, since those are the changes that matter in a `importRange` scenario (it's unilateral). – Henrique G. Abreu Jul 06 '14 at 23:36
1

I was facing the same issue and I don't think access can be granted through script. It needs to be done manually in each spreadsheet.

Best part is it needs to be done only once in any spreadsheet then it will update automatically.

Birudv
  • 95
  • 2
  • 8