如何在Google Sheet中将API返回的RSS数据拆分至多行多列？

Problem Description #

I'm trying to fetch data from the Coinspeaker IEO RSS feed (https://www.coinspeaker.com/ieo/feed/) and import it into Google Sheets. I've written the following callCoinSpeaker function:

function callCoinSpeaker() { 
var response = UrlFetchApp.fetch("https://www.coinspeaker.com/ieo/feed/"); 
Logger.log(response.getContentText()); 
var fact = response.getContentText(); 
var sheet = SpreadsheetApp.getActiveSheet(); 
sheet.getRange(1,1).setValue([fact]); 
}

The script runs without errors, but all the returned data is concentrated in cell A1. I'm looking for a code example that can automatically split the output into multiple rows and columns. Are there any examples of formatted API/RSS request outputs for this scenario?

Answer #

First off, the URL you're hitting returns an RSS XML feed, not plain text or JSON—so dumping the raw content into a single cell was never going to give you structured data. We need to parse that XML to extract individual entries and their specific fields.

Here's a revised script that handles the XML parsing, extracts key data points, and writes them neatly into rows and columns:

function callCoinSpeaker() {
  try {
    // Fetch the RSS feed content
    const response = UrlFetchApp.fetch("https://www.coinspeaker.com/ieo/feed/");
    const xmlContent = response.getContentText();
    
    // Parse the XML document using Google's XmlService
    const xmlDoc = XmlService.parse(xmlContent);
    const root = xmlDoc.getRootElement();
    const channel = root.getChild("channel");
    const items = channel.getChildren("item"); // Grab all individual post entries
    
    // Set up our output array with header rows
    const outputData = [
      ["Article Title", "URL", "Publish Date", "Short Description"]
    ];
    
    // Loop through each RSS item and extract relevant fields
    items.forEach(item => {
      // Extract each field (fallback to empty string if missing)
      const title = item.getChildText("title") || "";
      const link = item.getChildText("link") || "";
      const pubDate = item.getChildText("pubDate") || "";
      const description = item.getChildText("description") || "";
      
      // Add the extracted data as a new row in our output array
      outputData.push([title, link, pubDate, description]);
    });
    
    // Write the structured data to the sheet
    const sheet = SpreadsheetApp.getActiveSheet();
    sheet.clearContents(); // Optional: Clear old data before adding new
    sheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
    
    Logger.log(`Successfully imported ${items.length} articles!`);
  } catch (error) {
    Logger.log(`Error during import: ${error.message}`);
    SpreadsheetApp.getUi().alert(`Oops, something went wrong: ${error.message}`);
  }
}

What this does differently: #

• Proper XML Parsing: Uses Google Apps Script's built-in XmlService to decode the RSS structure, so we can access individual <item> elements and their child fields.
• Structured Data: Organizes extracted fields (title, link, date, description) into a 2D array, which is the format Google Sheets expects for bulk writes.
• Efficient Writing: Uses setValues() instead of setValue()—this is way faster for large datasets and ensures each piece of data lands in the correct cell.
• Error Handling: The try/catch block logs issues and alerts you if the feed is unavailable or the XML structure changes.

Quick customization tips: #

• Want more fields? Check the raw RSS output with Logger.log(xmlContent)—look for other child elements in <item> (like category or author) and add them to the extraction logic.
• Convert the publish date to a Sheets-friendly date object with new Date(pubDate) if you want to sort/filter by date easily.
• Target a specific sheet instead of the active one with SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YourSheetName").

内容的提问来源于stack exchange，提问作者Pierandrea