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 = '';
var CLIENT_SECRET = 'sziU...';

function getPubSubService() {
  return OAuth2.createService('SpreadsheetPubSub')
  // .setCache(CacheService.getUserCache())
  // .setLock(LockService.getUserLock())
  .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();

function logRedirectUri() {

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 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 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()) {
  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();

function closeSidebar() {
  var html = HtmlService.createHtmlOutput("<script>;</script>");

function makePubSubRequest(service, project, topic, data, attr) {
  var url = Utilities.formatString("", 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.


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