Google Sheets App Scripts: Make PubSub Publish Call

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')  .setAuthorizationBaseUrl('')  .setTokenUrl('')  .setClientId(CLIENT_ID)  .setClientSecret(CLIENT_SECRET)  .setCallbackFunction('authCallback')  .setPropertyStore(PropertiesService.getUserProperties())  // .setCache(CacheService.getUserCache())  // .setLock(LockService.getUserLock())  .setScope(['',''])  .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 authorizationfunction 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 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()) {    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>;</script>");  SpreadsheetApp.getUi().showSidebar(html);}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.


❤️ Is this article helpful?

Buy me a coffee ☕ or support my work via PayPal to keep this space 🖖 and ad-free.

Do send some 💖 to @d_luaz or share this article.

✨ By Desmond Lua

A dream boy who enjoys making apps, travelling and making youtube videos. Follow me on @d_luaz

👶 Apps I built

Travelopy - discover travel places in Malaysia, Singapore, Taiwan, Japan.