Google Sheets App Scripts: Make PubSub Publish Call

July 17, 2019

NOTE: Since Client Secret shall be exposed, therefore you should only apply this to your personal/trusted sheets only. Else, it would be better to write a cloud function which publish the message.

Setup Google App Scripts

Open App Scripts: Google Sheets -> Tools -> Script Editor

Setup OAuth2: Script Editor -> Resources -> Libraries, put 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF in Add Library and click Add. Select latest version and probably switch on the Development mode. Click Save.

NOTE: OAuth2 library for Google Apps Script

Create a new script file: File -> New -> Script File, name it GoogleService.

Add the following code.

var CLIENT_ID = '9016....apps.googleusercontent.com';
var CLIENT_SECRET = 'sziU...';

function getPubSubService() {
  return OAuth2.createService('SpreadsheetPubSub')
  .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/v2/auth')
  .setTokenUrl('https://oauth2.googleapis.com/token')
  .setClientId(CLIENT_ID)
  .setClientSecret(CLIENT_SECRET)
  .setCallbackFunction('authCallback')
  .setPropertyStore(PropertiesService.getUserProperties())
  // .setCache(CacheService.getUserCache())
  // .setLock(LockService.getUserLock())
  .setScope(['https://www.googleapis.com/auth/pubsub','https://www.googleapis.com/auth/script.external_request'])
  .setParam('access_type', 'offline')
  .setParam('approval_prompt', 'force')
  .setParam('login_hint', Session.getActiveUser().getEmail());
}

function authCallback(request) {
  var service = getPubSubService();
  var isAuthorized = service.handleCallback(request);
  if (isAuthorized) {
    closeSidebar(); // optional
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

// call this to re-test authorization
function reset() {
  var service = getPubSubService();
  service.reset();
}

function logRedirectUri() {
  Logger.log(OAuth2.getRedirectUri());
}

NOTE: Get CLIENT_ID and CLIENT_SECRET from the following section.

Create Google Cloud Credentials

Goto Google Cloud Console -> Credentials.

Click Create credentials -> OAuth client ID -> Web Application. Put Google Sheets or Google App Scripts as Name, at Authorised redirect URIs put in https://script.google.com/macros/d/SCRIPT_ID/usercallback. Click Save.

NOTE: Get SCRIPT_ID from Script Editor -> File -> Project Properties -> Script ID.

NOTE: You might need to setup OAuth consent screen.

Copy the client ID and client secret.

Make PubSub call

Create another script file: File -> New -> Script file or use the default Code.gs file to add the following function.

function onClick() {
  publishPubSub('coin168', 'bot_update', 'launch', {'action': 'refresh'})
}

function publishPubSub(project, topic, data, attr) {
  var service = getPubSubService();

  if (!service.hasAccess()) {
    showSidebar(service)
  }
  else {
    makePubSubRequest(service, project, topic, data, attr)
  }
}

function showSidebar(service) {
  var authorizationUrl = service.getAuthorizationUrl();
  var template = HtmlService.createTemplate(
    'Click <a href="<?= authorizationUrl ?>" target="_blank">Authorize</a>. This sidebar will automatically closed when authorization is complete.');
  template.authorizationUrl = authorizationUrl;
  var page = template.evaluate();
  SpreadsheetApp.getUi().showSidebar(page);
}

function closeSidebar() {
  var html = HtmlService.createHtmlOutput("<script>google.script.host.close();</script>");
  SpreadsheetApp.getUi().showSidebar(html);
}

function makePubSubRequest(service, project, topic, data, attr) {
  var url = Utilities.formatString("https://pubsub.googleapis.com/v1/projects/%s/topics/%s:publish", project, topic);

  var body = {
    messages: [
      {
        attributes: attr,
        data: Utilities.base64Encode(data)
      }
    ]
  };

  var response = UrlFetchApp.fetch(url, {
    method: "POST",
    contentType: 'application/json',
    muteHttpExceptions: true,
    payload: JSON.stringify(body),
    headers: {
      Authorization: 'Bearer ' + service.getAccessToken()
    }
  });

  var result = JSON.parse(response.getContentText());
  var message = JSON.stringify(result);
  return {
    log: message
  }
}

NOTE: Create a button to call onClick.

References:

This work is licensed under a
Creative Commons Attribution-NonCommercial 4.0 International License.