如何实现App Maker与电子表格的关联？新手技术求助

Hey there! As someone who’s tinkered with App Maker and Google Apps Script for a while, I can walk you through exactly how to set up this live connection and build your search form—no data import required. Let’s break it down step by step:

1. Set Up a Live Google Sheets Data Source in App Maker #

First, link your App Maker project directly to your Google Sheet (skip the data import):

• Open your App Maker project, head to the Data tab in the left sidebar.
• Click Add Data Source > Choose Google Sheets from the options.
• Follow the prompts to grant App Maker permission to access your Google Drive (this is needed to connect to your sheet).
• Pick the specific Google Sheet you want to use, and make sure you leave the "Import data" checkbox unchecked. This keeps the connection live—any changes to your sheet will automatically show up in the app later.

2. Build Your Search Form #

Next, put together the search UI on your App Maker page:

• Add an Input widget (name it searchInput so you can easily reference it in code).
• Add a Button widget (name it searchButton) and set its label to "Search" or your preferred text.
• Optional: Add a Table widget (name it resultsTable) to display matching data after the search runs.

3. Write the Apps Script Logic for Search #

Now, add code to your button’s click event to fetch matching data from the connected sheet:

1. Select the searchButton widget, go to the Events tab in the right sidebar.
2. Click Add Event > Click > Custom Function, then paste in this code (adjust details to match your sheet’s columns and widget names):

function onSearchButtonClick() {
  // Grab the search term from the input field
  const searchTerm = app.pages.YourSearchPageName.widgets.searchInput.value;
  
  // Access your connected Sheets data source (replace with your data source name)
  const sheetDS = app.datasources.YourSheetsDataSourceName;
  
  // Set up a filter to match your search term (replace "YourColumnName" with your sheet's actual column name)
  sheetDS.query.filters.YourColumnName._equals = searchTerm;
  
  // Load the filtered data and display it
  sheetDS.load(function() {
    // Check if any results were found
    if (sheetDS.items.length === 0) {
      app.showToast("No matching records found!", "Search Result", 3000);
    } else {
      // Link the filtered data to your results table
      app.pages.YourSearchPageName.widgets.resultsTable.datasource = sheetDS;
    }
  });
}

Quick Tips for Smooth Functionality #

• Column Name Match: Make sure the column name in your Google Sheet exactly matches the field name in your App Maker data source (App Maker uses the sheet’s first row as field names).
• Flexible Search: If you want partial matches instead of exact ones, replace _equals with _contains in the filter line.
• Empty Input Check: Add a quick check at the start of the function to handle empty search terms:

  if (!searchTerm.trim()) {
    app.showToast("Please enter a search term!", "Error", 3000);
    return;
  }
  

• Live Updates: Since this is a live connection, any edits to your Google Sheet will show up in the app the next time you run a search or reload the data source.

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