Hey there! Great to hear you’ve already nailed the core logic for your API integration—let’s wrap up the Google Sheets script to tie it all together with your spreadsheet row structure. Here’s a step-by-step implementation tailored to your setup:

1. Bind the Script to Your Row Buttons #

First, you’ll need a function that triggers when a row’s button is clicked, and can identify which row it’s acting on. Here’s how to set that up:

• In your Google Sheet, insert a button (via Insert > Drawing) for each row, or use a single reusable button (per-row buttons make it clearer for end users).
• Right-click the button, select Assign script, and enter the name of our function (we’ll call it processRow).

2. Function to Identify the Active Row & Read Values #

This part grabs the row number where the button was clicked, then pulls the two values you need (we’ll assume they’re in columns A: username and C: website—adjust the column letters to match your actual setup):

function processRow() {
  // Get the active sheet and the row where the button was clicked
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const activeRow = sheet.getActiveRange().getRow();

  // Read the two target values from the row (adjust columns as needed)
  const targetValue1 = sheet.getRange(`A${activeRow}`).getValue();
  const targetValue2 = sheet.getRange(`C${activeRow}`).getValue();

  // Skip if values are empty to avoid unnecessary API calls
  if (!targetValue1 || !targetValue2) {
    SpreadsheetApp.getUi().alert("Please fill in both required values!");
    return;
  }

  // Call your core API logic here
  const [result1, result2] = runCoreAPILogic(targetValue1, targetValue2);

  // Write results to the return value cells (columns F and G in this example)
  sheet.getRange(`F${activeRow}`).setValue(result1);
  sheet.getRange(`G${activeRow}`).setValue(result2);
}

3. Embed Your Core API & Calculation Logic #

Since you’ve already built the core part, drop it into a reusable function that takes the two input values and returns the two calculated results. Here’s a template to wrap your existing code:

function runCoreAPILogic(inputVal1, inputVal2) {
  // --- YOUR EXISTING CORE CODE STARTS HERE ---
  // Example API call structure (replace with your actual implementation)
  const apiUrl = `https://your-api-endpoint.com/data?param1=${encodeURIComponent(inputVal1)}&param2=${encodeURIComponent(inputVal2)}`;
  
  // Fetch API data (use UrlFetchApp for Google Sheets)
  const response = UrlFetchApp.fetch(apiUrl, {
    method: "GET",
    // Add headers/auth if required: headers: { "Authorization": "Bearer YOUR_TOKEN" }
  });
  
  // Parse response and calculate your two results
  const apiData = JSON.parse(response.getContentText());
  const returnValue1 = apiData.calculationOne; // Replace with your actual calculation
  const returnValue2 = apiData.calculationTwo; // Replace with your actual calculation
  // --- YOUR EXISTING CORE CODE ENDS HERE ---

  // Return both results as an array
  return [returnValue1, returnValue2];
}

4. Key Notes for Reliability #

• Permissions: The first time you run the script, you’ll need to grant authorization (Google requires this for scripts accessing external APIs or modifying sheets).
• Error Handling: Add try/catch blocks to handle API timeouts, rate limits, or invalid responses:

  try {
    const response = UrlFetchApp.fetch(apiUrl);
    // Rest of your logic
  } catch (error) {
    SpreadsheetApp.getUi().alert(`API call failed: ${error.message}`);
    return [null, null]; // Return empty values on failure
  }
  

• Column Adjustments: Double-check that all column references (A, C, F, G) match your actual spreadsheet structure. If you need to pull values from the dropdown columns instead, just swap those column letters in the getRange calls.

That’s it! This script will trigger per row, pull your target values, run your core API logic, and drop the results right into the designated return cells.

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