基于Google Apps Script的自定义Web表单ID查询实现问题

The main piece you're missing is passing the input value from your HTML form directly to your Google Apps Script function. Let's fix that and add the Google Sheets query functionality step by step.

1. Modify the HTML to Send the Input Value #

First, update your index.html to send the entered ID when the button is clicked, and tweak the result display to handle sheet data properly:

<!DOCTYPE html> 
<html> 
<head> 
<base target="_top"> 
<script> 
function onSuccess(results) { 
  const outputDiv = document.getElementById('output');
  
  // Handle empty results or display matching rows
  if (results.length === 0) {
    outputDiv.innerHTML = "<p>No matching entries found for that ID.</p>";
  } else {
    let resultHtml = "<h3>Matching Results:</h3><ul>";
    results.forEach(row => {
      resultHtml += `<li>${row.join(' | ')}</li>`;
    });
    resultHtml += "</ul>";
    outputDiv.innerHTML = resultHtml;
  }
} 
</script> 
</head> 
<body> 
<p style="margin-bottom: 16px; font-family: 'Verdana regular'; font-size: 16px;color: #727277;line-height: 16px;text-align: justify;">Ingrese ID: <br><br></p> 
<input type="text" id="ingresoid" size="20"> <!-- Swapped to standard "text" input type -->
<br><br> 
<input type="button" value="Buscar" onclick="google.script.run.withSuccessHandler(onSuccess).obtenerId(document.getElementById('ingresoid').value);" style="background-color:#C1D72E;border-radius:10px;padding:5px;color: #fff; border-color: #C1D72E;" /><br> 
<br> 
<div id="output"></div> 
</body> 
</html>

Key HTML Changes: #

• The onclick event now passes the input's value (document.getElementById('ingresoid').value) directly to the obtenerId function.
• Updated the onSuccess function to display results in a readable list, or show a message if no matches are found.
• Changed the input type from textbox to the standard text (more widely supported across browsers).

2. Update Apps Script to Receive the ID and Query Google Sheets #

Now modify your code.gs to accept the input ID, query your sheet, and return matching rows:

function doGet() { 
  return HtmlService.createHtmlOutputFromFile('index') 
    .setSandboxMode(HtmlService.SandboxMode.IFRAME); 
} 

// Accept the input ID as a parameter from the HTML
function obtenerId(idInput) { 
  // Replace these values with your actual sheet details
  const sheetId = "YOUR_GOOGLE_SHEET_ID";
  const sheetName = "Sheet1";
  
  try {
    // Open the sheet and fetch all data
    const sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
    const allData = sheet.getDataRange().getValues();
    
    // Filter rows where the first column (A) matches the input ID
    // Adjust row[0] to row[1] for column B, row[2] for C, etc.
    const matchingRows = allData.filter(row => row[0] == idInput);
    
    return matchingRows;
  } catch (error) {
    console.error("Error accessing sheet:", error);
    return []; // Return empty array if something goes wrong
  }
}

Key Apps Script Changes: #

• The obtenerId function now takes idInput as a parameter (the value sent from the HTML).
• Added code to open your Google Sheet, fetch all data, and filter rows that match the input ID. Adjust the column index (row[0]) if your ID is stored in a different column.
• Added basic error handling to catch issues like invalid sheet IDs or permission errors.

How It Works: #

1. When the user enters an ID and clicks the button, the HTML sends the input value to the obtenerId function in Apps Script.
2. obtenerId queries your Google Sheet for rows matching the ID.
3. The matching rows are sent back to the onSuccess function in the HTML, which displays them in the output div.

Don't forget to replace "YOUR_GOOGLE_SHEET_ID" with your actual sheet ID (found in the sheet's URL) and adjust the sheet name/column index to match your setup.

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