Error migrating data from GAS to google calendar "Too many calendar or calendar plans created in a short time"

When registering data with Google Calendar using GAS, the following errors occur frequently and I do not know what to do.

 Too many calendars or calendars created in a short time.
Please try again after a while.

The total number of calendar registration data is about 60,000.The above error appears in the 2000th case
After the second run, the number of registrations gradually decreases dramatically and eventually you cannot register.
I also set the trigger for less than 6 minutes per run, but this error message appears
Gsuite paid members
Adding Utilities.sleep(1000) between triggers also results in an error
Because of the large amount of data, I use maps instead of for

Could you give me some tips on how to solve this problem?

function func(){
  var start_time = new Date();
  // Obtain the currently active spreadsheet
  var spreadSheet=SpreadsheetApp.getActiveSpreadsheet();
  varsheet=spreadSheet.getSheetByName('Sheet1');
  vardd=sheet.getRange('A:E').getValues();
  var lastcol=6;

  var properties=PropertiesService.getScriptProperties(); // For progress saving
  var startRowKey = "startRow";
  var triggerKey = "trigger";

  var startRow=parseInt(properties.getProperty(startRowKey)));
  if(!startRow){
      // For the first run
      startRow = 1;
  }
  // Sheet description
  sheet.getRange(startRow+1,lastcol).setValue('start/'+start_time+'start line:'+startRow+'start');
  Logger.log('Start:'+startRow+'+Line 1 Start');

  varcal=CalendarApp.getDefaultCalendar();
  varn = 350;
  var endRow = 1500;

  if(startRow<=endRow){
      // Get only a few lines
      vardata=dd.slice(startRow, startRow+=n);
      try{
        var result=data.map(function(d) {varev=cal.createEvent('*'+d[0], new Date(d[1]), new Date(d[2]), {description:d[3], guests:d[4]}).setGuestsCanModify(true);});
        properties.setProperty (startRowKey, startRow); 
        setTrigger(triggerKey, "func");
        return;

      } catch(e){

        Logger.log (startRow + 'up to line '+' error content: '+e);
        sheet.getRange(startRow-1,lastcol).setValue('Error Content:'+e+'/End Time'+new Date()+'Line Number/'+startRow);
      }
  }

      Logger.log('Last Line End:'+startRow);
      sheet.getRange(startRow,lastcol).setValue('Finish/'+newDate()+'Last line:'+startRow);
      deleteTrigger(triggerKey);
      properties.deleteProperty (startRowKey);  
}

// Delete the trigger using the trigger ID stored in the specified key
function deleteTrigger(triggerKey){
  vartriggerId = PropertiesService.getScriptProperties().getProperty(triggerKey);

  if(!triggerId)return;

  ScriptApp.getProjectTrigger().filter(function(trigger){
    return trigger.getUniqueId() == triggerId;
  })
  .forEach(function(trigger){
    ScriptApp.deleteTrigger(trigger);
  });
  PropertiesService.getScriptProperties().deleteProperty(triggerKey);
}

// Issue Trigger
function setTrigger(triggerKey, funcName){
  deleteTrigger(triggerKey); // Delete any saved triggers
  vardt = new Date();
  dt.setMinutes(dt.getMinutes()+1); // Re-run after 1 minute
  vartriggerId=ScriptApp.newTrigger(funcName).timeBased().at(dt).create().getUniqueId();
  // Save the trigger ID to delete the trigger later
  PropertiesService.getScriptProperties().setProperty(triggerKey,triggerId);
}

google-apps-script google-cloud

2022-09-30 21:40

3 Answers

I'm reporting that I solved myself

Key resolution points are
·Use for statement instead of .map
·Add Utilities.sleep(1000); to the for statement
·About 500 cases per execution
·Sprinkle with minimal sauce!
(GAS seems weak in processing large amounts of data, so data processing is divided into separate programs and processed data is prepared)
(Reading and writing spreadsheets is heavy, so refrain from doing so or minimize the number of runs)
(Use the description also trigger() and remove unnecessary blanks in advance)
·Execute as an administrator

var spreadsheet=SpreadsheetApp.getActiveSpreadsheet();
varsheet=spreadSheet.getSheetByName('SheetName');
vardd=sheet.getDataRange().getValues();
// Get only some lines
var = dd.slice(1,3); // ★ Get line number slice(1,3) = line number 2-3
// calendar acquisition
varcal=CalendarApp.getDefaultCalendar();
for (vars=0;s<d.length;s++){
  cal.createEvent(d[s][1], new Date(d[s][2]), new Date(d[s][3]), {description:d[s][4], guests:d[s][5]}).setGuestsCanModify(true);
  Utilities.sleep (1000);
}

Thank you.

2022-09-30 21:40

There seems to be a limit if you plan more than 100,000 units.

If you create more than 100,000 calendars in a short period of time, you may not be able to edit them for several hours.
It may take several months for these types of restrictions to be completely reset.
Restrictions on using the calendar - G Suite Administrator Help

I couldn't find it as long as I checked the help, but maybe there was a limitation caused by running the API intensively in a short period of time, and the above error occurred (60,000x2) after the second time?

2022-09-30 21:40

I inadvertently registered a continuous event with a for statement, and the situation became similar.
More than 1,000 errors occurred, and then "Please try again after a while," so I passed 24 hours, but the error was not resolved and I lost control of my calendar.

Suddenly, I had a casual idea and tried to implement and execute the description of the event (I couldn't change the date and title), and the error stopped from that moment on.

I still don't know why it was resolved, but once it was not possible to use the same or very similar content as the event that caused the error, a major change in the implementation from there may have been a clue to the solution.

2022-09-30 21:40

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656