如何为IFTTT更新的Google Sheet设置触发脚本？

Hey there! I totally get your frustration—simple onEdit() triggers don’t play nice with API-driven changes like those from IFTTT, since they only fire when a user manually edits the sheet. Let’s walk through two solid solutions to get your data processing working:

1. Use an Installable onChange Trigger #

This is the most direct fix because installable triggers can detect changes made by APIs, scripts, or even other automation tools (unlike simple onEdit()). Here’s how to set it up:

Step 1: Write Your Processing Function #

Open your Google Sheet, go to Extensions > Apps Script, and paste this example code (adjust the logic to fit your needs):

function handleIftttUpdates(e) {
  // Only respond to edit-type changes (includes API writes)
  if (e.changeType !== 'EDIT') return;

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Grab the last row (assuming IFTTT appends new rows)
  const lastRow = sheet.getLastRow();
  const rowData = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];

  // Add your custom processing logic here
  Logger.log("Processing new IFTTT data: " + rowData);
  
  // Example: Format the first column to uppercase
  if (rowData[0]) {
    sheet.getRange(lastRow, 1).setValue(rowData[0].toUpperCase());
  }
}

Step 2: Create the Installable Trigger #

1. In the Apps Script editor, click the clock icon (Triggers) on the left sidebar.
2. Click Add Trigger in the bottom right.
3. Configure the trigger like this:
   • Choose which function to run: handleIftttUpdates
   • Choose which deployment to run: Head
   • Select event source: From spreadsheet
   • Select event type: On change
4. Click Save and follow the prompts to authorize the script (you’ll need to allow access to your Google Sheet).

2. Use a Time-Driven Trigger (For Non-Real-Time Needs) #

If you don’t need instant processing, a scheduled trigger can periodically check for new rows added by IFTTT. This is great if you’re worried about trigger limits or missed events.

Step 1: Write the Scheduled Check Function #

function checkForNewIftttRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  // Use cell A1 to track the last row we processed (set initial value to 0 manually)
  const lastProcessedRow = sheet.getRange("A1").getValue() || 0;

  if (lastRow > lastProcessedRow) {
    // Get all new rows since the last check
    const newRows = sheet.getRange(lastProcessedRow + 1, 1, lastRow - lastProcessedRow, sheet.getLastColumn()).getValues();
    
    // Process each new row
    newRows.forEach((row, index) => {
      const currentRow = lastProcessedRow + 1 + index;
      // Add your processing logic here
      if (row[1]) {
        // Example: Format a date column
        sheet.getRange(currentRow, 2).setNumberFormat("MM/dd/yyyy");
      }
    });

    // Update the last processed row marker
    sheet.getRange("A1").setValue(lastRow);
  }
}

Step 2: Set Up the Scheduled Trigger #

1. Go to the Triggers menu in Apps Script.
2. Click Add Trigger.
3. Configure it:
   • Function to run: checkForNewIftttRows
   • Event source: Time-driven
   • Type of time based trigger: Minute timer (or choose hourly/daily based on your needs)
   • Minute interval: Every 5 minutes (adjust as needed)
4. Save and authorize the script.

Quick Notes #

• Installable triggers have daily quota limits (e.g., 1000 onChange triggers per day), which is more than enough for most personal use cases.
• To avoid infinite loops (if your processing edits the sheet), add checks to only process rows that came from IFTTT (e.g., look for a specific tag in one of the columns).

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