Retrieving User Information with Shared Spreadsheet

27 Apr 2017, 14:34

GasTips

Google Apps Script / spreadsheet

This sample script retrieves the user information which is editing the shared spreadsheet.

It was found as follows.

User information retrieving by Class Session is the owner and users which installed triggers by themselves.
When each user installs a trigger, user information retrieving by Class Session losts the accuracy. So user information has to be retrieved using a temporally installed trigger.
Using onOpen(), it cannot directly install triggers and authorize.
Using menu bar, it can install triggers and authorize Google Services using API.

Here, I thought 2 problems.

The confirmation whether the authorization was done.
- At onOpen(), although many methods using Google API can be executed without the authorization, there are also some methods which cannot be executed without the authorization. Furthermore, there are some methods which cannot execute even if the authorization was done. It’s trigger. On the other hand, DriveApp requires the authorization for only the first time, but it can use without the authorization after 2nd times.
I thought that users can find easily by displaying information in a dialog box when spreadsheet is launched. So I adopted displaying information using the dialog box. But, there is a big limitation for the dialog box.
- Using a click of button on a dialog box, it can install triggers. However it cannot authorize Google Services using API.

Using above information, I thought a flow to retrieve user information.

When user opens the spreadsheet for the first time, it displays ‘Please authorize at “Authorization” of menu bar.’ using a dialog box, and creates a menu bar “Authorization”.
The user clicks “OK” button on the dialog box and run “Authorization” at the menu bar. By running “Authorization”, the user information is retrieved by a temporally installed trigger.
When the user opens the spreadsheet after the 2nd time, the authorization is checked by DriveApp. A dialog box with ‘Push OK button.’ is displayed. By clicking “OK”, the user information is retrieved by a temporally installed trigger.

By this flow, the user information which is editing the shared spreadsheet can be retrieved. Although I think that there may be also other solutions, I proposal this as one of solutions.

Script :

function showDialog(html, title){
  SpreadsheetApp.getActiveSpreadsheet().show(
    HtmlService
    .createHtmlOutput(html)
    .setTitle(title)
    .setWidth(400)
    .setHeight(100)
  );
}

function getUser(){
  var triggerId = ScriptApp.newTrigger('getUser')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create()
    .getUniqueId();
  var userInf = Session.getEffectiveUser();
  CacheService.getUserCache().putAll({
    "username": userInf.getUsername(),
    "usermail": userInf.getEmail()
  }, 7200);
  var triggers = ScriptApp.getProjectTriggers();
  [ScriptApp.deleteTrigger(i) for each (i in triggers) if (i.getUniqueId() == triggerId)];
}

function dialogForGetUser(){
  showDialog('<input type="button"\
      value="OK"\
      onclick="google.script.run.withSuccessHandler(function(){google.script.host.close()})\
      .getUser()">',
      'Push OK button.'
  );
}

function dialogForAuth(){
  showDialog('<input type="button"\
      value="OK"\
      onclick="google.script.host.close()">',
      'Please authorize at "Authorization" of menu bar.'
  );
}

function getAuth() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.removeMenu("Authorization");
  getUser();
  ss.toast("Done.", "Authorization", 3);
}

function onOpen(){
  try {
    var temp = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId())
    dialogForGetUser();
  } catch(e) {
    dialogForAuth();
    SpreadsheetApp.getActiveSpreadsheet().addMenu(
      "Authorization",
      [{
        functionName:"getAuth",
        name:"Run this only when the first time"
      }]
    );
  }
}

When the spreadsheet is opened, at first, onOpen() is executed. It is checked whether the user has already authorized. The dialog box is as follows.

If the user has never authorized yet, dialogForAuth() is executed. If the user has already authorized. dialogForGetUser() is executed. The dialog box is as follows.

The user information is retrieved as follows. You can use this by onEdit() and trigger.

var user = CacheService.getUserCache().getAll(["username", "usermail"]);

Retrieving User Information with Shared Spreadsheet

Share!