hacker_news.gs

const spreadSheetId = PropertiesService.getScriptProperties().getProperty('spreadSheetId');
const sheetName = 'topstories';
var sheetObj = SpreadSheetsSQL.open(spreadSheetId, sheetName);
const openApiKey = PropertiesService.getScriptProperties().getProperty('openApiKey');
const openApiUrl = 'https://api.openai.com/v1/chat/completions';

function getNewHackerNews() {
  var storyIds = getTopStoryIds();
  var stories = [];
  
  var existStoryIds = getExistStoryIds(storyIds);

  var now = new Date();
  var updates = Object.fromEntries(sheetObj.select(['id', 'update']).result().map(x => [x.id, x.update]));
  
  for (var id of storyIds) {
    if (existStoryIds.indexOf(id) == -1) {
      var story = getStory(id);
      if(story.url !== undefined) {
        // 人工知能に関連した記事タイトルか判定する
        story['related_ai'] = getRelatedAI(story.title);

        stories.push(deleteStoryElem(story));
      }
    }
    else {
      // 1日以上経過した記事のscoreを更新
      var dt = updates[id];
      dt.setDate(dt.getDate() + 1);
      if (now > dt) {
        sheetObj.updateRows({ score: story.score, update: now }, 'id = ' + id);
      }
    }
  }
  
  // 記事を保存
  addStory(stories);

  // １週間以上前の保存した記事を削除
  deleteStory();

  // score順にソート
  sheetObj.sheet_.sort(3, false);
}

// すでに取得済みの記事IDを取得
function getExistStoryIds(storyIds) {
  var idStr = storyIds.join(',');
  
  var rows = sheetObj.select(['id']).filter('id IN ' + idStr).result();
  return rows.map(function(row, index){
    return row.id;
  });
}

// 記事データの不要な要素を削除
function deleteStoryElem(story) {
  delete story['deleted'];
  delete story['type'];
  delete story['text'];
  delete story['dead'];
  delete story['parent'];
  delete story['kids'];
  delete story['parts'];
  delete story['descendants'];
  
  return story;
}

// 人工知能に関連した記事タイトルか判定する
function getRelatedAI(title) {
  //リクエストデータの設定
  const headers = {
    'Authorization':'Bearer '+ openApiKey,
    'Content-type': 'application/json'
  };
  const options = {
    'headers': headers, 
    'method': 'POST',
    'payload': JSON.stringify({
      'model': 'gpt-3.5-turbo',
      'max_tokens' : 8,
      "temperature": 0.0,
      'messages': [
        {"role": "system", "content": "Answer Yes or No if the article is related to artificial intelligence"},
        {"role": "user", "content": title}
      ]})
  };

  //リクエストを送信し、結果取得
  try {
    const response = JSON.parse(UrlFetchApp.fetch(openApiUrl, options).getContentText());
    const resMessage = response.choices[0].message.content.trim();
    if (resMessage.toLowerCase().match(/yes/)) {
      return true;
    }
    else {
      return false;
    }
  } catch(e) {
    return e.message;
  }
}

// 記事を取得
function getStory(id) {
  var url = "https://hacker-news.firebaseio.com/v0/item/" + id + ".json";
  var json = UrlFetchApp.fetch(url).getContentText();
  var jsonData = JSON.parse(json);
  
  let dateTime = new Date(jsonData.time * 1000);
  jsonData['time_jp'] = dateTime;
  jsonData['update'] = new Date();
  jsonData['title_ja'] = '=GOOGLETRANSLATE("' + jsonData.title + '", "en", "ja")';
  return jsonData;
}

// 記事を保存
function addStory(stories) {
  sheetObj.insertRows(stories);
}

// 最新人気記事IDを取得
function getTopStoryIds() {
  var url = "https://hacker-news.firebaseio.com/v0/topstories.json";
  var json = UrlFetchApp.fetch(url).getContentText();
  var jsonData = JSON.parse(json);
  
  return jsonData;
}

// １週間以上前の保存した記事を削除
function deleteStory() {
  var today = new Date();
  var beforeWeek = new Date();
  beforeWeek.setDate(today.getDate() - 7);
  var beforeWeekTime = Math.floor(beforeWeek.getTime() / 1000);
  
  sheetObj.deleteRows('time < ' + beforeWeekTime);
}