🕸️ GAS Web Scraping in Action: Scheduled Currency and Stock Market Data Retrieval

Many people think Google Apps Script (GAS) can only "manipulate" cells in spreadsheets.
But in reality, GAS comes with a superpower: UrlFetchApp.

This means your Google Sheet can "connect to the internet" and fetch data from around the world! Combined with GAS's free Time-driven Triggers, you can build a zero-cost, fully automated web scraper that runs on a daily schedule.

In this chapter, we'll write a scraper that fetches the latest USD exchange rate from Bank of Taiwan every morning at 9 AM and writes it to your spreadsheet.

1. Meet the Ultimate Weapon: `UrlFetchApp`

In regular JavaScript, we use fetch() or axios to call APIs.
But in GAS, there's no fetch. Instead, Google provides its own UrlFetchApp.fetch().

Basic Syntax:

// Fetch raw HTML (for scraping)
var response = UrlFetchApp.fetch("https://example.com");
var html = response.getContentText();

// Call an API to get JSON
var apiResponse = UrlFetchApp.fetch("https://api.exchangerate-api.com/v4/latest/USD");
var data = JSON.parse(apiResponse.getContentText());

2. Hands-on: Fetching Bank of Taiwan's USD Exchange Rate API

There are many free exchange rate APIs online. Here, we'll use a public one to get "how many TWD equals 1 USD."

Step 1: Create a Spreadsheet and Open GAS Editor

Open a new Google Sheet and name it "My Financial Dashboard."
Enter "Date" in A1 and "USD Exchange Rate" in B1.
Click Extensions -> Apps Script in the top menu.

Step 2: Write the Scraper Script

Paste the following code into your Code.gs:

// Main function to fetch data and write to the sheet
function fetchDailyExchangeRate() {
  // 1. Specify the API URL (a free public exchange rate API)
  var apiUrl = "https://api.exchangerate-api.com/v4/latest/USD";
  
  try {
    // 2. Make the request to fetch data
    var response = UrlFetchApp.fetch(apiUrl);
    var jsonString = response.getContentText();
    
    // 3. Convert the text to a JavaScript object
    var data = JSON.parse(jsonString);
    
    // 4. Extract the USD-to-TWD exchange rate
    var usdToTwd = data.rates.TWD;
    
    // Error handling if data is missing
    if (!usdToTwd) {
      Logger.log("No TWD exchange rate found!");
      return;
    }
    
    Logger.log("Today's USD exchange rate: " + usdToTwd);
    
    // 5. Prepare data to write to the sheet: [today's date, exchange rate]
    var today = new Date();
    // Format date as YYYY/MM/DD
    var dateString = Utilities.formatDate(today, Session.getScriptTimeZone(), "yyyy/MM/dd");
    
    // 6. Append the data to the bottom of the sheet
    appendToSheet(dateString, usdToTwd);
    
  } catch (error) {
    Logger.log("Scraper error: " + error.toString());
  }
}

// Helper function to write to the sheet
function appendToSheet(date, rate) {
  // Get the currently bound sheet (first sheet)
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  
  // Append a new row with the data (array format: [Column A, Column B])
  sheet.appendRow([date, rate]);
  
  Logger.log("✅ Data written to sheet successfully!");
}

Step 3: Manual Testing

Click the "Run" button (make sure to select fetchDailyExchangeRate as the function).
On first run, Google will show an "Authorization Required" warning because your script needs permission to access external networks.
Click Review Permissions -> Select your account -> Advanced -> Go to (unsafe) and grant permission.

After authorization, switch back to your Google Sheet. You'll see today's date and USD exchange rate magically appear in the second row!

3. Advanced Scraping: Adding Headers or Spoofing User-Agent

Some financial websites block bots. To bypass this, you may need to spoof a browser or include an API key.

UrlFetchApp accepts a second parameter for advanced options:

function fetchWithHeaders() {
  var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC";
  
  var options = {
    "method": "get",
    // Spoof User-Agent or include API keys
    "headers": {
      "X-CMC_PRO_API_KEY": "YOUR_API_KEY_HERE",
      "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36"
    },
    // Prevent crashes on 404/500 errors (default throws an error)
    "muteHttpExceptions": true 
  };

  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response.getContentText());
}

💡 Pro Tip: If scraping Taiwanese websites (e.g., PTT or TWSE), remember to set the encoding to UTF-8 or Big5, or the HTML will appear as gibberish!

4. Ultimate Automation: Setting Up Time-driven Triggers

The code works, but who wants to manually click "Run" every morning?
Let's set up an alarm for Google's servers to run this code automatically.

In the GAS editor, click the "alarm clock" icon (Triggers) in the left menu.
Click the blue Add Trigger button at the bottom right.
Configure as follows:
- Function to run: fetchDailyExchangeRate
- Deployment: Head
- Event source: Time-driven
- Trigger type: Day timer
- Time: 9 AM to 10 AM
Click Save.

Mission Accomplished! 🎉

From now on, every morning around 9 AM, Google's servers will silently run your scraper, fetch the latest USD exchange rate, and neatly insert it into your spreadsheet!

This is the beauty of automation: write the code once, and the machine works for you forever.
In the next chapter, we'll enhance this scraper to send you a LINE notification when new data is fetched!