Trying to create events using Sheets/Calendar for users in our Workspace

The events must be owned by the user so that they have ownership of the Meet to control access to invitees outside of Google. The user doesn't manage the creation of the event though - this is done by another team

The events can be created easily enough, but not with the ownership. Any way around this?

Using an installable trigger allows the function creating the event to be executed by the user. The problem is two-fold:

the trigger needs to be owned by the user, so onOpen or onEdit, so
the call to the CalendarApp to get the user's calendar fails (see error below)

Exception: The script doesn't have permission to perform that action. Required permission: (https://www.googleapis.com/auth/calendar || https://www.googleapis.com/auth/calendar.readonly || https://www.google.com/calendar/feeds)

minimal reproducible example:

const USER = '[email protected]';
const CAL = CalendarApp.getCalendarById(USER);

function createSlots() {        // data comes from Sheet
  var event = CAL.createEvent(
    'slot',
    new Date('2022-12-05T12:00Z'), 
    new Date('2022-12-05T13:00Z'),
    {
      guests: USER // includes user as a guest to create the Meet code
    }
  );
}

function makeTrigger() {
  var trigger = ScriptApp.newTrigger('createSlots')
    .forSpreadsheet(SHEET)
    .onOpen()
    .create();
}

Strangely - in doing the minimal reproducible example I thought about triggering the function createSlots directly instead of inside another function. This works!

So having the code as the post works, but my original effectively was

const USER = '[email protected]';
const CAL = CalendarApp.getCalendarById(USER);

function createSlots() {        // data comes from Sheet
  var event = CAL.createEvent(
    'slot',
    new Date('2022-12-05T12:00Z'), 
    new Date('2022-12-05T13:00Z'),
    {
      guests: USER // includes user as a guest to create the Meet code
    }
  );
}

function onOpen() {
    createSlots;
}

function makeTrigger() {
  var trigger = ScriptApp.newTrigger('onOpen')
    .forSpreadsheet(SHEET)
    .onOpen()
    .create();
}

CodePudding user response：

Yes you can do this. However you will need to use a service account and

Once properly configured the service account will be able to login and impersonate the users on your domain.

setup

In app script under library you will need to include Oauth the script id is 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF.

The service account you created open the credetinals.json file and find the private key and the client email you will needs those in this script.

Note this sample just lists a users primary calendar but i can see that it is listing for different users when i change the delegated user. I assume you can get the rest working you just need the authorization part.

// Oauth script id 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF


function run() {

  var email = '[email protected]';

  var service = getDomainWideDelegationService('Calendar: ', 'https://www.googleapis.com/auth/calendar', email);

  if (!service.hasAccess()) {

    Logger.log('failed to authenticate as user '   email);      

  } else Logger.log('successfully authenticated as user '   email);

   Logger.log('access token '   service.getAccessToken())    

   getCalendar(service)

}



function getCalendar(service){


var requestBody                = {};
  requestBody.headers            = {'Authorization': 'Bearer '   service.getAccessToken()};
  requestBody.contentType        = "application/json";
  requestBody.method             = "GET";
  requestBody.muteHttpExceptions = false;  

  var url = 'https://www.googleapis.com/calendar/v3/users/me/calendarList/primary';

  var calendarGetResponse = UrlFetchApp.fetch(url, requestBody);


   Logger.log('response '   calendarGetResponse)    

}

// these two things are included in the .JSON file that you download when creating the service account and service account key
var OAUTH2_SERVICE_ACCOUNT_PRIVATE_KEY = '[REDACTED]';
var OAUTH2_SERVICE_ACCOUNT_CLIENT_EMAIL = '[REDACTED]';



function getDomainWideDelegationService(serviceName, scope, email) {

  Logger.log('starting getDomainWideDelegationService for email: '   email);

  return OAuth2.createService(serviceName   email)
    // Set the endpoint URL.
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')

    // Set the private key and issuer.
    .setPrivateKey(OAUTH2_SERVICE_ACCOUNT_PRIVATE_KEY)
    .setIssuer(OAUTH2_SERVICE_ACCOUNT_CLIENT_EMAIL)

    // Set the name of the user to impersonate. This will only work for
    // Google Apps for Work/EDU accounts whose admin has setup domain-wide
    // delegation:
    // https://developers.google.com/identity/protocols/OAuth2ServiceAccount#delegatingauthority
    .setSubject(email)

    // Set the property store where authorized tokens should be persisted.
    .setPropertyStore(PropertiesService.getScriptProperties())

    // Set the scope. This must match one of the scopes configured during the
    // setup of domain-wide delegation.
    .setScope(scope);

}

The key to the above code is the line .setSubject(email) this is where you define which user the service account is to delegate as.