Use Slack API Bot to retrieve message texts and put them into google sheet

By using GPT, I was able to create the slack bot and have the token ready. But this below lines does not work.

I tried the tester within this https://api.slack.com/methods/conversations.history to make sure the token is valid. I suspect the URL below does not work but not sure how to correct this.

If you have an easier way to achieve my goal of retrieving data and putting them into Google Sheet, please let me know know

function getConversationHistory() {
  var token = "YOUR_SLACK_API_TOKEN";
  var channel = "CHANNEL_ID";
  var url = "https://slack.com/api/conversations.history?token=" + token + "&channel=" + channel;
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  var messages = data.messages;
  for (var i = 0; i < messages.length; i++) {
    var message = messages[i];
    var text = message.text;
    var ts = message.ts;
    // do something with the message data
  }
}

I believe your goal is as follows.

• You want to put the retrieved values var url = "https://slack.com/api/conversations.history?token=" + token + "&channel=" + channel; from slack to Google Spreadsheet.
• From your showing script, you want to retrieve the values of text and ts.
• Your access token and the channel ID are valid values.

In this case, how about the following modification?

Modified script:

In this case, please copy and paste the following script to the script editor of Google Spreadsheet and save the script after you confirmed the values of token and channel.

function getConversationHistory() {
  var token = "YOUR_SLACK_API_TOKEN";
  var channel = "CHANNEL_ID";
  var url = "https://slack.com/api/conversations.history?token=" + token + "&channel=" + channel;
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());

  // I modified the below script.
  var headers = ["text", "ts"];
  var values = [headers, ...data.messages.map(o => headers.map(h => o[h] ? (h == "ts" ? new Date(o[h] * 1000) : o[h]) : null))];
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set the sheet name.
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

• When this script is run, the values of text and ts from url are put to "Sheet1" of the Spreadsheet.

Reference:

• map()