能否发布带每日定时API触发的Google Apps Script为Google Sheet插件并自动发通知？

Your exact use case—daily automated API calls, change detection, and email notifications—fits perfectly within what Google Sheet add-ons can do, though there are a few key implementation details to keep in mind to make it work smoothly for end users.

Here’s a breakdown of how to approach it:

1. Core Components You’ll Need #

a. Menu-Based Setup for Triggers #

Since installable time triggers can’t be created automatically when a user installs the add-on (due to authorization restrictions), you’ll need to prompt users to set up the daily trigger manually via a custom menu in the Sheet.

Add an onOpen() function to register a menu:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('My Data Tracker')
    .addItem('Enable Daily Updates', 'setupDailyTrigger')
    .addItem('Disable Daily Updates', 'deleteDailyTrigger')
    .addToUi();
}

b. Trigger Management Functions #

Create functions to create and delete the daily installable trigger:

function setupDailyTrigger() {
  // Check if trigger already exists to avoid duplicates
  const existingTriggers = ScriptApp.getProjectTriggers().filter(trigger => 
    trigger.getHandlerFunction() === 'runDailyUpdate'
  );
  
  if (existingTriggers.length === 0) {
    ScriptApp.newTrigger('runDailyUpdate')
      .timeBased()
      .everyDays(1)
      .atHour(9) // Set your preferred time (in the user's timezone)
      .create();
      
    SpreadsheetApp.getUi().alert('Daily updates enabled! You’ll get emails when new data is detected.');
  } else {
    SpreadsheetApp.getUi().alert('Daily updates are already enabled.');
  }
}

function deleteDailyTrigger() {
  const triggers = ScriptApp.getProjectTriggers().filter(trigger => 
    trigger.getHandlerFunction() === 'runDailyUpdate'
  );
  
  triggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));
  SpreadsheetApp.getUi().alert('Daily updates disabled.');
}

c. The Daily Update Logic #

This is your core function that fetches data, checks for changes, and sends notifications:

function runDailyUpdate() {
  try {
    // 1. Fetch data from your API
    const apiResponse = UrlFetchApp.fetch('YOUR_API_ENDPOINT', {
      // Add any necessary headers/auth here
    });
    const newData = JSON.parse(apiResponse.getContentText());
    
    // 2. Retrieve last stored data state (use PropertiesService for persistence)
    const userProperties = PropertiesService.getUserProperties();
    const lastStoredData = userProperties.getProperty('lastData');
    const lastData = lastStoredData ? JSON.parse(lastStoredData) : null;
    
    // 3. Detect changes (customize this logic based on your data structure)
    const hasNewInfo = checkForNewInformation(lastData, newData);
    
    if (hasNewInfo) {
      // 4. Send email notification to the user
      const userEmail = Session.getActiveUser().getEmail();
      MailApp.sendEmail({
        to: userEmail,
        subject: 'New Data Detected!',
        body: 'Hello,\n\nWe’ve found new information in your data feed. Check your Google Sheet for details.\n\nBest,\nMy Data Tracker Add-on'
      });
      
      // Update stored data to the latest version
      userProperties.setProperty('lastData', JSON.stringify(newData));
    }
  } catch (error) {
    // Optional: Send error notification to yourself or log the issue
    console.error('Daily update failed:', error);
  }
}

// Custom function to compare old and new data (adjust to your needs)
function checkForNewInformation(oldData, newData) {
  if (!oldData) return true; // First run, consider all data as new
  
  // Example: Compare the number of entries or a timestamp
  return newData.entries.length > oldData.entries.length;
}

2. Key Considerations for Publishing #

• Authorization Scopes: Ensure your appsscript.json manifest includes all required scopes, such as:

  • https://www.googleapis.com/auth/spreadsheets (for accessing the Sheet)
  • https://www.googleapis.com/auth/script.scriptapp (for triggers)
  • https://www.googleapis.com/auth/script.send_mail (for email)
  • https://www.googleapis.com/auth/script.external_request (for API calls)

• Data Persistence: Using PropertiesService.getUserProperties() is ideal for storing per-user data state (since each user’s trigger runs independently). Alternatively, you can use a hidden sheet tab if you need to store larger datasets.

• Error Handling: Adding try/catch blocks in runDailyUpdate() prevents the trigger from being automatically disabled if an error occurs (like API downtime).

• Testing: Before publishing publicly, test the add-on privately with a small group of users to iron out any authorization or logic issues.

• Marketplace Requirements: Follow Google’s Workspace Marketplace guidelines, including providing a clear description, privacy policy, and compliant branding.

3. Publishing Steps #

Once your script is ready:

• Go to the Google Apps Script editor > Publish > Deploy as Google Workspace Add-on
• Follow the prompts to configure your add-on (set logo, description, privacy policy link, etc.)
• Choose to publish privately first (for testing) or publicly to the Marketplace

This setup will let users install your add-on, enable the daily trigger via the menu, and receive automatic email notifications when new data is detected.

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