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 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 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: