1

I'm desperately trying to pull Gmail notifications via the PubSub service to populate a Spreadsheet on Google Drive. I hit a wall trying to implement the push function, so I looked up generating a pull function instead and thought I would set it on a timer. I created the code below and tested it against some specific words in the body of new messages, which works great. I also shared out the GAS script file, which is on Google Drive, so a coworker can run it as well.

I thought I would finish up by setting the timed trigger so this could run unattended for me. Then I decided to run the primary function one last time, and I got this error:

Error: { "error": { "code": 403, "message": "User not authorized to perform this action.", "status":

Huh? I just got this function to work with permissions 5 minutes ago! Worse still, I took the timer back off, and the error is still there! I'm stuck!

To get this to work, I have published the GAS webapp to the Chrome store, registered the domain on Google cloud platform, created a service account and loaded the JSON key to my webapp, created a topic/subscription, and granted publish permissions to Gmails service account. I successfully tested it about 5 times before I enacted the trigger.

Here is the code I am using across two files with the PubSub and GS libraries:

Main.GS

var EMAILID = Session.getActiveUser().toString();

var PROJECTID = 'project-id-4467804683870657159';
var TOPIC = 'projects/project-id-4467804683870657***/topics/pullTrigger';
var SUBS = 'projects/project-id-4467804683870657***/subscriptions/pullTest';

function doGet(){
  
  var NumOfMessagesToPull = 50;
  
  PubSubApp.setTokenService(getTokenService());
  
  var sub = PubSubApp.SubscriptionApp(PROJECTID);
  
  var mysub = sub.getSubscription('pullTest');

  var subs = mysub.pull(NumOfMessagesToPull);
  
  if(subs.length > 0){
    
    for(var i=0; i < subs.length;i++){
      
      var subData = JSON.parse(Utilities.newBlob(Utilities.base64Decode(subs[i].message.data)).getDataAsString());
      var messageData = {
        ackId: subs[i].ackId,
        publishTime: subs[i].message.publishTime,
        emailAddress: subData.emailAddress,
        historyId: subData.historyId,
        messageId: subs[i].message.messageId
      };
      
      var mBatch = new Array();
      var hBatch = Gmail.Users.History.list(EMAILID, {'startHistoryId': messageData.historyId}).history;
      
      for(var h = 0; h < hBatch.length; h++)
      {
        //Logger.log(hBatch[h]);
         
        var messageIds = getMessageIds(hBatch[h].messages);
        for(var e = 0; e < messageIds.length; e++)
        {
          mBatch.push(messageIds[e]);
        }
      }
      //Logger.log(mBatch);
      
      for(var m = 0; m < mBatch.length; m++)
      {
        var thisMessage = GmailApp.getMessageById(mBatch[m]);
        var email = {from: thisMessage.getFrom().toString(),
                     body: thisMessage.getPlainBody().toString(),
                   subject: thisMessage.getSubject().toString()};
                         //thisMessage.moveToTrash();
        
        Logger.log(email.subject.indexOf('Expiration'));
        if(/*(email.from.toString().includes('info@mydomain.com'))&&(*/email.subject.indexOf('Expiration') > 0)//)
        {
          var startInd = email.body.indexOf('Start')+12;
          var endInd = email.body.indexOf('End')+12;
          var pasInd = email.body.indexOf('Number')+8;
          var qualInd = email.subject.indexOf('Q');
          //Logger.log(startInd+ endInd+ pasInd);  
          
             var qualco = email.subject.substring(qualInd, qualInd+17);
             var warning = email.body.substring(email.body.indexOf('Episode Expires in'),email.body.indexOf('days')+4);
             var startDate = email.body.substring(startInd, startInd+12);
             var endDate = email.body.substring(endInd , endInd+11);
             var priorAuthNumber = email.body.substring(pasInd , pasInd+11);
          
             var ss = SpreadsheetApp.openById('1dfIALtc2XEaFG_ewqo6qq6VlmqO3RohX4llUfI1zbc8').getSheets()[0];
             ss.appendRow([qualco, priorAuthNumber, startDate, endDate, warning]);  

        }

      }
      
    }
  }
}


function getMessageIds(messageBatch){
  var idList = new Array()
  
  for(var i = 0; i < messageBatch.length; i++)
  {
    //Logger.log(messageBatch[i]);
   idList.push(messageBatch[i].id);
  }
 // Logger.log(idList);
  return idList;
}



function enrollEmail(emailAddr){
  var email = emailAddr || EMAILID;
  PubSubApp.setTokenService(getTokenService())
  var topicName = PubSubApp.PublishingApp(PROJECTID).getTopicName('pullTrigger')
  Logger.log(watchEmail(topicName,{labelIds:["INBOX"], email:email}));
}

Helpers.GS

function addGmailPolicy(Policy){
  return PubSubApp.policyBuilder()
  [(Policy)?"editPolicy":"newPolicy"](Policy)
  .addPublisher("SERVICEACCOUNT", 'gmail-api-push@system.gserviceaccount.com')
  .getPolicy();
}

function addDomainSubs(Domain,Policy){
  return PubSubApp.policyBuilder()
  [(Policy)?"editPolicy":"newPolicy"](Policy)
  .addPublisher("DOMAIN", Domain)
  .getPolicy();
}

function getSubscriptionPolicy(){
  return PubSubApp.policyBuilder()
  .newPolicy()
  .addSubscriber("DOMAIN","mydomain.com")
}


function watchEmail(fullTopicName,watchOptions){
  var options = {email: EMAILID,token:ScriptApp.getOAuthToken(),labelIds:[]};
  
  for(var option in watchOptions){
    if(option in options){
      options[option] = watchOptions[option];
    }
  }
   Logger.log(options);
  var url = "https://www.googleapis.com/gmail/v1/users/"+options.email+"/watch"
  
  var payload = {
    topicName: fullTopicName,
    labelIds: options.labelIds
  }
  
  var params = {
    method:"POST",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    headers:{Authorization: "Bearer "+ options.token
    },
    muteHttpExceptions:true
  }
  
   var results = UrlFetchApp.fetch(url, params);
  
  if(results.getResponseCode() != 200){
     throw new Error(results.getContentText())
  }else{
    return JSON.parse(results.getContentText());
  }
  
 }

function CreateTopic(topicName) {
  var topic;
  PubSubApp.setTokenService(getTokenService());
  var pubservice = PubSubApp.PublishingApp(PROJECTID);
  try{topic = pubservice.newTopic(topicName)}
  catch(e){topic = pubservice.getTopic(topicName);}
  return topic;  
}

function CreateSubscription(subscriptionName,topicName,webhookUrl){
  var sub;
  PubSubApp.setTokenService(getTokenService());
  var subService = PubSubApp.SubscriptionApp(PROJECTID);
  try{sub = subService.newSubscription(subscriptionName,topicName,webhookUrl)}
  catch(e){sub = subService.getSubscription(subscriptionName,topicName,webhookUrl)}
  return sub;
}


function getTokenService(){
  var jsonKey = JSON.parse(PropertiesService.getScriptProperties().getProperty("jsonKey"));  
  var privateKey = jsonKey.private_key;
  var serviceAccountEmail = jsonKey.client_email; 
  var sa = GSApp.init(privateKey, ['https://www.googleapis.com/auth/pubsub'], serviceAccountEmail);
  sa.addUser(serviceAccountEmail)
  .requestToken();
  return sa.tokenService(serviceAccountEmail);
}


function requestGmailScope_(){GmailApp.getAliases()}

Can anyone help me find out how adding a timed trigger would break authentications?

I posted a similar topic regarding the push version of this feature. I'll close whichever topic gets a working solution first.

Thanks so much for your help!

Community
  • 1
  • 1
Nathaniel MacIver
  • 387
  • 1
  • 4
  • 21
  • Possible duplicate of [Need Help creating GMAIL Pub/Sub Notification service to SpreadsheetApp (Google Appscript)](https://stackoverflow.com/questions/43010915/need-help-creating-gmail-pub-sub-notification-service-to-spreadsheetapp-google) – Nathaniel MacIver Jul 04 '17 at 21:04

0 Answers0