ChatGPT를 활용하여 구글 시트에서 Apps Script로 구글 뉴스 가져오기 2탄

지난 번에 하나의 키워드로 뉴스를 검색해서 가지고 오는 것을 만들어 보았다. 이번에는 3개의 키워드를 가지고 기사를 검색해서 메일을 받는 것을 ChatGPT를 통해서 만들어 본다.

구글 시트는 다음과 같이 설정하였다.

 

[첫번째 질문]

나는 구글 스프레드 시트의 앱스 스크립트 기능을 활용해서 어제와 오늘 한글 기사를 news.google.com에서 추출을 하고 내 이메일로 받고 싶어.

검색할 키워드는 3개이고, 검색할 기사의 수는 다음과 같아.

첫번째 키워드는 B2에 있고, 첫번째 키워드에 대한 검색할 기사의 수는 B3에 있어.
두번째 키워드는 C2에 있고, 두번째 키워드에 대한 검색할 기사의 수는 C3에 있어.
세번째 키워드는 D2에 있고, 세번째 키워드에 대한 검색할 기사의 수는 D3에 있어.

그리고 이메일 주소는 B4에 있어.

기사들은 다음의 표 형식을 받고 싶어
│ 번호 │ 뉴스 제목 │ 뉴스 기사 링크 │언론사 │ 기자이름 │ 발행일 │

표는 3개로 키워드별로 구분을 해 줘. 보기에 깔끔하게 만들어 줘.

앱스 스크립트로로 어떻게 하면 되는지 알려줘.

[질문 번역결과]
I want to use Google Spreadsheet's Apps Script function to extract yesterday's and today's Korean articles from news.google.com and send them to my email.

There are 3 keywords to search, and the number of articles to search is as follows.

The first keyword is in B2, and the number of articles to search for the first keyword is in B3.
The second keyword is in C2, and the number of articles to search for the second keyword is in C3.
The third keyword is in D2, and the number of articles to search for the third keyword is in D3.

And the email address is on B4.

Articles want to receive the following table format
│ Number │ Title of News │ Link to News Article │ Media │ Name of Reporter │ Date of Publication │

The table is divided into three keywords. Make it look neat.

Please tell me how to do it with Apps Script.

 

[ChatGPT 응답]
To achieve your goal of extracting yesterday's and today's Korean articles from news.google.com and sending them to your email in the specified table format, you can use Google Apps Script. Here's a step-by-step guide on how to do it:

Step 1: Set up the Google Apps Script project

1. Open your Google Spreadsheet.
2. Click on "Extensions" in the top menu and select "Apps Script" to open the Google Apps Script editor.

Step 2: Write the Apps Script code Replace the existing code in the script editor with the following code:

function fetchAndSendNewsArticles() {
  // Get keywords and number of articles to search for each keyword
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var keywords = sheet.getRange("B2:D2").getValues()[0];
  var numArticles = sheet.getRange("B3:D3").getValues()[0];

  // Get email address
  var emailAddress = sheet.getRange("B4").getValue();

  // Date calculation for yesterday and today
  var today = new Date();
  var yesterday = new Date(today.getTime() - 24 * 60 * 60 * 1000);
  var dateYesterday = yesterday.toISOString().slice(0, 10);
  var dateToday = today.toISOString().slice(0, 10);

  // Email body content
  var emailBody = "Articles from " + dateYesterday + " to " + dateToday + "\n\n";

  // Loop through each keyword and fetch news articles
  for (var i = 0; i < keywords.length; i++) {
    var keyword = keywords[i];
    var numArticle = numArticles[i];

    // Fetch articles for the current keyword
    var url = "https://news.google.com/rss/search?q=" + encodeURIComponent(keyword) + "&hl=ko&gl=KR&ceid=KR:ko";
    var response = UrlFetchApp.fetch(url);
    var xml = response.getContentText();
    var document = XmlService.parse(xml);
    var items = document.getRootElement().getChildren("channel")[0].getChildren("item");

    // Create a table for the current keyword
    emailBody += "\n" + keyword + ":\n";
    emailBody += "│ Number │ Title of News │ Link to News Article │ Media │ Name of Reporter │ Date of Publication │\n";

    // Loop through articles and add them to the email body
    for (var j = 0; j < numArticle && j < items.length; j++) {
      var article = items[j];
      var title = article.getChild("title").getText();
      var link = article.getChild("link").getText();
      var media = article.getChild("source").getText();
      var reporter = ""; // Not available in the RSS feed
      var date = new Date(Date.parse(article.getChild("pubDate").getText()));
      var dateStr = Utilities.formatDate(date, "GMT+9", "yyyy-MM-dd HH:mm");

      emailBody += "│ " + (j + 1) + " │ " + title + " │ " + link + " │ " + media + " │ " + reporter + " │ " + dateStr + " │\n";
    }
  }

  // Send the email
  var subject = "News Articles from Google News";
  MailApp.sendEmail(emailAddress, subject, emailBody);
}

Step 3: Save and run the script

1. Save the script by clicking on the floppy disk icon or pressing Ctrl + S.
2. To run the script, click on the play button (▶️) or press Ctrl + Enter.

Step 4: Authorize the script If this is the first time you're running the script, you'll be prompted to authorize it. Click on the "Review Permissions" button and follow the instructions to grant the necessary permissions for the script to access your Google Sheets and send emails.

Step 5: Test the script To test the script, you can simply call the fetchAndSendNewsArticles function from the script editor. It will fetch the articles and send them to the email address specified in cell B4 of the spreadsheet.

Step 6: Set up a time-driven trigger (optional) If you want this script to run automatically at a specific time each day, you can set up a time-driven trigger in the Apps Script editor. Here's how:

1. Click on the clock icon ⏰ in the left sidebar of the script editor.
2. Click on "+ Add Trigger" in the bottom right corner.
3. Configure the trigger settings:
   • Choose which function to run: fetchAndSendNewsArticles
   • Choose which deployment should run: "Head"
   • Select event source: "Time-driven"
   • Select type of time-based trigger: "Day timer"
   • Select time of day: Choose the desired time for the script to run daily.
4. Click on "Save."

The script will now run automatically at the specified time each day, fetching the news articles and sending them to the specified email address in the table format.

 

[실행결과]

이메일로 잘 받을 수 있었다. 그러나 보기에 불편해서 다시 ChatGPT에 수정을 요구하였다.

 

[두번째 질문]

표가 깨져서 나타나, 보기에 깔끔하게 만들어 줘.

[질문 번역결과]
The table appears broken, making it look neat.

 

[ChatGPT 응답]

Apologies for the formatting issue.
In Google Sheets, the email content may not retain the proper formatting
when sent directly as plain text.
To ensure the table appears neat and well-formatted in the email,
we can use HTML formatting for the email body.
Here's the updated code to format the email with an HTML table:

 

function fetchAndSendNewsArticles() {
  // Get keywords and number of articles to search for each keyword
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var keywords = sheet.getRange("B2:D2").getValues()[0];
  var numArticles = sheet.getRange("B3:D3").getValues()[0];

  // Get email address
  var emailAddress = sheet.getRange("B4").getValue();

  // Date calculation for yesterday and today
  var today = new Date();
  var yesterday = new Date(today.getTime() - 24 * 60 * 60 * 1000);
  var dateYesterday = yesterday.toISOString().slice(0, 10);
  var dateToday = today.toISOString().slice(0, 10);

  // Email body content
  var emailBody = "<h2>Articles from " + dateYesterday + " to " + dateToday + "</h2>";

  // Loop through each keyword and fetch news articles
  for (var i = 0; i < keywords.length; i++) {
    var keyword = keywords[i];
    var numArticle = numArticles[i];

    // Fetch articles for the current keyword
    var url = "https://news.google.com/rss/search?q=" + encodeURIComponent(keyword) + "&hl=ko&gl=KR&ceid=KR:ko";
    var response = UrlFetchApp.fetch(url);
    var xml = response.getContentText();
    var document = XmlService.parse(xml);
    var items = document.getRootElement().getChildren("channel")[0].getChildren("item");

    // Create a table for the current keyword
    emailBody += "<h3>" + keyword + "</h3>";
    emailBody += "<table border='1' cellpadding='10' style='border-collapse:collapse;'>";
    emailBody += "<tr><th>Number</th><th>Title of News</th><th>Link to News Article</th><th>Media</th><th>Name of Reporter</th><th>Date of Publication</th></tr>";

    // Loop through articles and add them to the email body
    for (var j = 0; j < numArticle && j < items.length; j++) {
      var article = items[j];
      var title = article.getChild("title").getText();
      var link = article.getChild("link").getText();
      var media = article.getChild("source").getText();
      var reporter = ""; // Not available in the RSS feed
      var date = new Date(Date.parse(article.getChild("pubDate").getText()));
      var dateStr = Utilities.formatDate(date, "GMT+9", "yyyy-MM-dd HH:mm");

      emailBody += "<tr><td>" + (j + 1) + "</td><td>" + title + "</td><td>" + link + "</td><td>" + media + "</td><td>" + reporter + "</td><td>" + dateStr + "</td></tr>";
    }

    emailBody += "</table>";
  }

  // Send the email
  var subject = "News Articles from Google News";
  MailApp.sendEmail({
    to: emailAddress,
    subject: subject,
    htmlBody: emailBody
  });
}

 

Now, the email will be sent in HTML format
with a well-formatted table for each keyword, making it look neat and organized.

 

[실행결과]

키워드 별로 구분되어서 보기 편하게 만들어졌다.

질문을 하다보니 원하는 결과를 잘 얻지 못하는 부분이 있었고, 질문을 계속 다듬어서 할 필요가 있었다. 그래야 적은 질문 으로 좋은 결과를 얻을 수 있었다.

키워드에 띄워쓰기가 들어가니 오류가 있어서 추가한다.

[세번째 질문]

키워드를 변경하니 다음과 같은 오류가 나타나.
Exception: Error on line 1: Open quote is expected for attribute "face" associated
with an  element type  "font".
fetchAndSendNewsArticles    @ Code.gs:29
해결을 해 줘.

[질문 번역결과]
After changing the keyword, the following error appears.
Exception: Error on line 1: Open quote is expected for attribute "face" associated with an element type "font".
fetchAndSendNewsArticles@Code.gs:29
Please solve it.

 

[ChatGPT 응답]

Apologies for the inconvenience. It seems that the error is related to the usage of the "font" tag, which is not supported in the current version of HTML used by Gmail for composing emails. To fix this issue, we'll need to replace the "font" tag with CSS styles for font formatting.

Here's the updated code that removes the "font" tag and uses CSS for font formatting:

 

function fetchAndSendNewsArticles() {
  // Get keywords and number of articles to search for each keyword
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var keywords = sheet.getRange("B2:D2").getValues()[0];
  var numArticles = sheet.getRange("B3:D3").getValues()[0];

  // Get email address
  var emailAddress = sheet.getRange("B4").getValue();

  // Date calculation for yesterday and today
  var today = new Date();
  var yesterday = new Date(today.getTime() - 24 * 60 * 60 * 1000);
  var dateYesterday = yesterday.toISOString().slice(0, 10);
  var dateToday = today.toISOString().slice(0, 10);

  // Email body content
  var emailBody = "<h2 style='font-family: Arial, sans-serif;'>Articles from " + dateYesterday + " to " + dateToday + "</h2>";

  // Loop through each keyword and fetch news articles
  for (var i = 0; i < keywords.length; i++) {
    var keyword = keywords[i];
    var numArticle = numArticles[i];

    // Fetch articles for the current keyword
    var url = "https://news.google.com/rss/search?q=" + encodeURIComponent(keyword) + "&hl=ko&gl=KR&ceid=KR:ko";
    var response = fetchWithRetries(url, 3); // Retry the request up to 3 times
    if (response) {
      var xml = response.getContentText();
      var document = XmlService.parse(xml);
      var items = document.getRootElement().getChildren("channel")[0].getChildren("item");

      // Create a table for the current keyword
      emailBody += "<h3 style='font-family: Arial, sans-serif;'>" + keyword + "</h3>";
      emailBody += "<table border='1' cellpadding='10' style='border-collapse:collapse; font-family: Arial, sans-serif;'>";
      emailBody += "<tr><th>Number</th><th>Title of News</th><th>Link to News Article</th><th>Media</th><th>Name of Reporter</th><th>Date of Publication</th></tr>";

      // Loop through articles and add them to the email body
      for (var j = 0; j < numArticle && j < items.length; j++) {
        var article = items[j];
        var title = article.getChild("title").getText();
        var link = article.getChild("link").getText();
        var media = article.getChild("source").getText();
        var reporter = ""; // Not available in the RSS feed
        var date = new Date(Date.parse(article.getChild("pubDate").getText()));
        var dateStr = Utilities.formatDate(date, "GMT+9", "yyyy-MM-dd HH:mm");

        emailBody += "<tr><td>" + (j + 1) + "</td><td>" + title + "</td><td>" + link + "</td><td>" + media + "</td><td>" + reporter + "</td><td>" + dateStr + "</td></tr>";
      }

      emailBody += "</table>";
    } else {
      emailBody += "<p>Error fetching articles for keyword: " + keyword + "</p>";
    }
  }

  // Send the email
  var subject = "News Articles from Google News";
  MailApp.sendEmail({
    to: emailAddress,
    subject: subject,
    htmlBody: emailBody
  });
}

function fetchWithRetries(url, maxRetries) {
  var retry = 0;
  while (retry < maxRetries) {
    try {
      var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      return response;
    } catch (e) {
      Utilities.sleep(2000); // Wait for 2 seconds before retrying
      retry++;
    }
  }
  return null;
}

 

With these changes, the email should be properly formatted, and the "font" tag issue should be resolved.

 

추가 사용해 보면서 오류가 나오면 업데이트 하려고 한다.