Plaid与Google Apps Script集成咨询：银行交易数据同步至谷歌表格

Great question! Integrating Plaid with Google Apps Script (GAS) to sync bank transactions to Google Sheets is totally doable, even though Plaid’s SDK is focused on Node.js. The key challenge is handling Plaid Link (the front-end auth flow) since GAS is server-side, but we can work around that with a simple web interface built using GAS’s HtmlService. Here’s a step-by-step implementation plan with code examples:

• Sign up for a Plaid account and get your core credentials: client_id, secret, and select your environment (Sandbox for testing, Development for production use).
• Create a new Google Sheet where you want to store transaction data.
• Link a new Google Apps Script project to your sheet via Extensions > Apps Script.

Never hardcode sensitive credentials in your script. Use GAS’s Script Properties to store them safely:

• Run this function once to set your credentials (replace placeholder values with your actual Plaid details):

function setPlaidCredentials() {
  const props = PropertiesService.getScriptProperties();
  props.setProperty('PLAID_CLIENT_ID', 'your_client_id');
  props.setProperty('PLAID_SECRET', 'your_secret');
  props.setProperty('PLAID_ENV', 'sandbox'); // Update to 'development'/'production' later
}

Plaid Link requires a front-end to let users connect their bank accounts. We’ll create a simple web page using GAS’s HtmlService to handle this flow:

3.1 Create the Front-End HTML File #

Add a new HTML file to your GAS project (File > New > Html file) named LinkPage:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="https://cdn.plaid.com/link/v2/stable/link-initialize.js"></script>
  </head>
  <body>
    <h3>Connect Your Bank Account</h3>
    <button id="plaid-link-button" style="padding: 10px 20px; font-size: 16px;">Connect with Plaid</button>

    <script>
      // Fetch a Link Token from the GAS backend to initialize Plaid Link
      google.script.run.withSuccessHandler(initializePlaidLink)
          .getLinkToken();

      function initializePlaidLink(linkToken) {
        const plaidHandler = Plaid.create({
          token: linkToken,
          onSuccess: (publicToken, metadata) => {
            // Send the public token to GAS to exchange for a permanent access token
            google.script.run.withSuccessHandler(() => {
              alert('Bank account connected successfully! You can close this window now.');
            }).exchangePublicToken(publicToken);
          },
          onExit: (error, metadata) => {
            console.log('Link flow exited:', error, metadata);
            alert('Failed to connect your bank account. Please try again.');
          }
        });

        // Trigger Plaid Link when the button is clicked
        document.getElementById('plaid-link-button').addEventListener('click', () => {
          plaidHandler.open();
        });
      }
    </script>
  </body>
</html>

3.2 Add Back-End Functions for Token Handling #

Add these functions to your main GAS script file to support the Link flow:

Generate a Plaid Link Token #

function getLinkToken() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('PLAID_CLIENT_ID');
  const secret = props.getProperty('PLAID_SECRET');
  const env = props.getProperty('PLAID_ENV');
  const baseUrl = env === 'sandbox' ? 'https://sandbox.plaid.com' : 'https://development.plaid.com';

  const payload = JSON.stringify({
    client_id: clientId,
    secret: secret,
    user: { client_user_id: 'your_unique_user_id' }, // Use a unique identifier like your email
    client_name: 'Google Sheets Transaction Sync',
    products: ['transactions'],
    country_codes: ['US'], // Adjust based on your region (e.g., ['CA'] for Canada)
    language: 'en'
  });

  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: payload
  };

  try {
    const response = UrlFetchApp.fetch(`${baseUrl}/link/token/create`, options);
    const data = JSON.parse(response.getContentText());
    return data.link_token;
  } catch (error) {
    console.error('Error generating Link Token:', error);
    throw new Error('Failed to generate Link Token. Check your credentials and try again.');
  }
}

Exchange Public Token for Permanent Access Token #

function exchangePublicToken(publicToken) {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('PLAID_CLIENT_ID');
  const secret = props.getProperty('PLAID_SECRET');
  const env = props.getProperty('PLAID_ENV');
  const baseUrl = env === 'sandbox' ? 'https://sandbox.plaid.com' : 'https://development.plaid.com';

  const payload = JSON.stringify({
    client_id: clientId,
    secret: secret,
    public_token: publicToken
  });

  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: payload
  };

  try {
    const response = UrlFetchApp.fetch(`${baseUrl}/item/public_token/exchange`, options);
    const data = JSON.parse(response.getContentText());
    // Store the access token for future transaction requests
    props.setProperty('PLAID_ACCESS_TOKEN', data.access_token);
    return true;
  } catch (error) {
    console.error('Error exchanging public token:', error);
    throw new Error('Failed to exchange public token. Check your credentials and try again.');
  }
}

Deploy the Web Interface #

• Click Deploy > New deployment
• Select "Web app" as the deployment type
• Set "Execute as" to "Me"
• Set "Who has access" to "Only myself" (since this is for your personal use)
• Click "Deploy" and copy the web app URL. Open it in a browser to connect your bank account.

Add this function to pull transactions from Plaid and write them to your sheet (with duplicate prevention):

function fetchAndSyncTransactions() {
  const props = PropertiesService.getScriptProperties();
  const clientId = props.getProperty('PLAID_CLIENT_ID');
  const secret = props.getProperty('PLAID_SECRET');
  const accessToken = props.getProperty('PLAID_ACCESS_TOKEN');
  const env = props.getProperty('PLAID_ENV');
  const baseUrl = env === 'sandbox' ? 'https://sandbox.plaid.com' : 'https://development.plaid.com';

  if (!accessToken) {
    throw new Error('No access token found. Please connect your bank account first via the web interface.');
  }

  // Get or create the Transactions sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let transactionsSheet = ss.getSheetByName('Transactions');
  if (!transactionsSheet) {
    transactionsSheet = ss.insertSheet('Transactions');
    // Add header row
    transactionsSheet.appendRow([
      'Transaction ID', 'Date', 'Merchant Name', 'Amount', 'Category', 'Account Name', 'Status'
    ]);
  }

  // Fetch all transactions (handle pagination with next_cursor)
  let nextCursor = null;
  let allTransactions = [];
  const startDate = '2023-01-01'; // Adjust to your desired start date
  const endDate = new Date().toISOString().split('T')[0];

  do {
    const payload = JSON.stringify({
      client_id: clientId,
      secret: secret,
      access_token: accessToken,
      start_date: startDate,
      end_date: endDate,
      cursor: nextCursor
    });

    const options = {
      method: 'post',
      contentType: 'application/json',
      payload: payload
    };

    try {
      const response = UrlFetchApp.fetch(`${baseUrl}/transactions/get`, options);
      const data = JSON.parse(response.getContentText());
      allTransactions = [...allTransactions, ...data.transactions];
      nextCursor = data.next_cursor;
    } catch (error) {
      console.error('Error fetching transactions:', error);
      throw new Error('Failed to fetch transactions. Check your access token and try again.');
    }
  } while (nextCursor !== null);

  // Remove duplicates using transaction IDs
  const existingIds = new Set(transactionsSheet.getDataRange().getValues()
    .slice(1) // Skip header row
    .map(row => row[0]));

  const newTransactions = allTransactions.filter(transaction => !existingIds.has(transaction.transaction_id));

  // Prepare rows for sheet insertion
  const rows = newTransactions.map(transaction => [
    transaction.transaction_id,
    transaction.date,
    transaction.name,
    transaction.amount,
    transaction.category.join(', '),
    transaction.account_name,
    transaction.status
  ]);

  // Append new transactions to the sheet
  if (rows.length > 0) {
    transactionsSheet.getRange(transactionsSheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
    console.log(`Successfully synced ${rows.length} new transactions`);
  } else {
    console.log('No new transactions to sync');
  }
}

To keep your sheet updated without manual runs:

• In your GAS project, go to Edit > Current project's triggers
• Click Add trigger
• Select fetchAndSyncTransactions as the function to run
• Choose "Time-driven" as the event source
• Pick your preferred sync frequency (e.g., daily at 9 AM)
• Save the trigger

• Testing First: Use Plaid’s Sandbox environment with test credentials (e.g., user_good / pass_good) to validate the flow before moving to production.
• Error Handling: Expand the basic error handling to send email alerts using MailApp.sendEmail() if syncs fail.
• Privacy: Ensure your GAS project and Google Sheet are only shared with trusted users, as they store sensitive financial data links.
• Rate Limits: Check Plaid’s API rate limits to avoid hitting thresholds, especially if syncing multiple accounts frequently.

内容的提问来源于stack exchange，提问作者Emma F.