1

Task: For each of our clients, we need to build eight spreadsheets with a minimum of two triggers each: 1) on Open and 2) Time based to refresh specific data from a larger spreadsheet. (This is a workaround for failing IMPORTRANGE functions.)

We currently have 100 clients, and are expanding to 200 shortly. Therefore, our current need is approximately 800 spreadsheets, and 1,600 triggers; projected to be 1,600 and 3,200 respectively very soon.

Problem After creating the first 300 spreadsheets (approximately 600 triggers) we have received the following error:

Your Quota of Triggers has been exceeded. To Create more, you must delete some existing triggers.

Desired Solution: We are collecting the eight spreadsheets on a private Google site for each client. Ideally we could invoke each spreadsheet script from the site, and eliminate time-based triggers. Tried creating a button to execute the script, but on the site, it is not interactive.

Questions: Operating on Windows 7, error is in Chrome also tried Firefox, and got same error.

1)a Does anyone know of the limits on triggers, is it published somewhere?

1)b Is there a way to increase these limits?

2) Any ideas on another work around or solution?

Community
  • 1
  • 1
user1393960
  • 11
  • 2
  • 5

2 Answers2

2

Eric already answered your question (1). (No, and no)

Now to question (2), a possible workaround is to invert the scenario of your scripts, instead of each script fetch its data on the "larger spreadsheet", a single script on your master could distribute the data on all other spreadsheets. You could have some kind of "registration" of each spreadsheet, maybe place them in the same folder, or any other way that suit you.

By changing the approach to this one, you'll surely run into another limitation, which is the script total run time: 6 minutes. But this problem you can easily work around by splitting the task into minor chunks that you can work and resume on a every minute trigger.

Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
  • Thanks for the quick response. We looked at the push option instead of the pull, and went with the pull as a default. I guess the push would work as you mentioned...We're pretty good at editing scripts, but not very experienced in writing one from scratch. Do you know of any published push scripts that we could use as a starting point? thanks! – user1393960 May 14 '12 at 16:20
  • 1
    No, not really. But it is basically a loop to open each remote spreadsheet `SpreadsheetApp.openById('ss-key')` and then `setValues` on the desired sheet.range. Nothing really different from what you do to pull (besides the loop to do it many times). – Henrique G. Abreu May 14 '12 at 16:57
  • I think I'm 90% there on this. – user1393960 May 17 '12 at 09:30
  • I've gone through both the read and write spreadsheet data using javascript objects...everything is going well, but i need two changes that I can't seem to get right. This part of the script writes data to the correct sheet, and creates a new sheet if it cannot find an existing one..I only want it to write to a sheet if it matches one of the propertys-not create any new ones. – user1393960 May 17 '12 at 09:44
  • var headerBackgroundColor = dataSheet.getRange(1, 1).getBackgroundColor(); for (var i = 0; i < property.length; ++i) { var target = SpreadsheetApp.openById("0Ak0WS_0ujwvsdEJyUDFWcjdfZzRqa3ZtX29tR2kzNVE"); var sheet = target.getSheetByName(property[i]) || target.insertSheet(property[i], ss.getSheets().length); sheet.clear(); var headersRange = sheet.getRange(1, 1, 1, columnNames.length); headersRange.setValues([columnNames]); headersRange.setBackgroundColor(headerBackgroundColor); setRowsData(sheet, dataByproperty[property[i]]); } – user1393960 May 17 '12 at 09:45
  • Lastly, I need to loop it through the other 99 spreadsheets, each with a unique sheet name that matches one of the propertys. Not sure if I should just copy this portion and paste to repeat, or if its possible to loop it with just a list of the different spreadsheet keys. Probably easier ways to do this, but the tutorial was the best place i could find to start... Thanks again for any advice. – user1393960 May 17 '12 at 09:50
  • Surely loop, copy 'n paste 99 times is just horrible. I advise you to create a new question, with your specific doubts on this, possibly simplifying the problem/script a little, down to the core issue, so you get help more easily. – Henrique G. Abreu May 17 '12 at 15:06
0

There are limits to the number of triggers one user can have, but they aren't published and they can't be changed.

Eric Koleda
  • 12,420
  • 1
  • 33
  • 51