Create stackoverflow inbox unread messages list in (Google Sheets) to create a bot in Telegram and receive these notifications

Question

To fetch unread inbox messages, there is this API:

https://api.stackexchange.com/docs/inbox-unread

{
  "items": [],
  "has_more": false,
  "quota_max": 10000,
  "quota_remaining": 9998
}

I'd like to ask for help getting me to list these inbox alerts in a Google Sheets. Through a script, I confess that I can not assemble one to be able to do this function of lists the messages.

The idea is to get this list and then create a function via script to send these alerts to my Telegram like this:

function EnviarTelegram(botSecret, chatId, body) {
var response = UrlFetchApp.fetch("https://api.telegram.org/bot" + botSecret + "/sendMessage?text=" + encodeURIComponent(body) + "&chat_id=" + chatId + "&parse_mode=HTML");
}

And that way, I would get something like this there on Telegram, as I already have for RSS Feeds:

But instead of receiving notifications of new questions, I want to receive my inbox notifications when someone answers my question, when I get points and so on.

Answer 1

• You want to retrieve own unread comments and reputation on Stackoverflow using Stackexchange API.
• You want to put the retrieved values to the Spreadsheet.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Usage:

1. Retrieve access token

In order to retrieve the unread comments and reputation, it is required to use the access token.

Flow:

1. Register an application at stackapps.com.
   • Input "Application Name", "Description", "OAuth Domain" and "Application Website".
     • As a sample credential, I inputted them as "sampleApp", "This is a sample app.", "localhost" and "sample".
     • In order to use as the redirect uri, please set "localhost" to "OAuth Domain".
   • Copy "Client Id", "Client Secret" and "Key". "Key" is also required to use the API.
2. Authorize the scopes.
   • Please create the following URL and access it using your browser. Please set your client_id.
   • https://stackexchange.com/oauth?client_id=#####&scope=no_expiry%20read_inbox&redirect_uri=http://localhost
     • In this script, "no_expiry" and "read_inbox" are used as the scopes. These scopes are used for retrieving the unread comments.
   • When the created URL is opened by your browser, please click "Approve" as follows.
   • Copy the code ### code ### from URL of browser like http://localhost/?code=### code ###.

3. Retrieve access token using "Client Id", "Client Secret" and "Code". Please run the following curl command. By this, you can retrieve access token like access_token=#####.

   • This access token has no expiration time because "no_expiry" is included in the scopes. So you can continue to use this access token.

     curl \
         -d "client_id=#####" \
         -d "client_secret=#####" \
         -d "code=#####" \
         -d "redirect_uri=http://localhost" \
         "https://stackexchange.com/oauth/access_token"
     

2. Run script

Sample script 1:

This sample script retrieves the unread comments.

function myFunction() {
  var key = "###"; // Please set your key.
  var accessToken = "###"; // Please set your access token.
  var spreadsheetId = "###"; // Please set the Spreadsheet ID.

  var url = "https://api.stackexchange.com/2.2/inbox/unread?filter=withbody&pagesize=100&access_token=" + accessToken + "&key=" + key;
  var comments = JSON.parse(UrlFetchApp.fetch(url).getContentText());

  var headers = ["creation_date", "link", "title", "body"];
  var values = comments.items.map(function(item) {return headers.map(function(h) {return item[h]})});
  values.unshift(headers);
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Sheet1");
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Sample script 2:

This sample script retrieves the reputation history.

function myFunction() {
  var key = "###"; // Please set your key.
  var accessToken = "###"; // Please set your access token.
  var userId = "###"; // Please set your user ID on Stackoverflow.
  var spreadsheetId = "###"; // Please set the Spreadsheet ID.

  var url = "https://api.stackexchange.com/2.2/users/" + userId + "/reputation-history/full?site=stackoverflow&pagesize=100&access_token=" + accessToken + "&key=" + key;
  var reputation = JSON.parse(UrlFetchApp.fetch(url).getContentText());

  var headers = ["creation_date", "reputation_change", "post_id"];
  var values = reputation.items.map(function(item) {return headers.map(function(h) {return item[h]})});
  values.unshift(headers);
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Sheet1");
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

• When you use this, you can retrieve the change of your reputation by checking the date and time.

Note:

• This is a sample script. So please modify this for your actual situation.
• Unfortunately, in the current stage, it seems that there are no methods in the API for automatically notifying when new comment was got. So as one workaround for Google Apps Script, I'm running the script using the time-driven trigger. By this, I can know the new comments. Also the change of reputation can be known.

References:

• Stack Exchange API v2.2
• Notifying Comments at Stackoverflow by Email using Google Apps Script

If I misunderstood your question and this was not the result you want, I apologize.

Updated: March 19, 2020

From January, 2020, the access token cannot be used with the query parameter like access_token=###. Ref So please use the access token to the request header instead of the query parameter. It's as follows.

var res = UrlFetchApp.fetch(url, {headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}});