如何实现Google Sheets数据POST至API后根据响应更新指定单元格？

阿华AIGC实验室

2026-5-14

Solution to Automate Google Sheets Data Submission & Status Update

Got it, let's get your workflow up and running properly. Your core logic is solid, but there are a few tweaks needed to handle API responses correctly and update the right cell in your sheet. Here's the breakdown:

First, Fix Key Issues in Your Original Code

You were passing the raw data object as payload instead of the stringified payload variable
You need to handle potential API request errors (like network failures or 4xx/5xx status codes) with a try-catch block
Your ideal logic targets the entire row instead of a specific column for the success/false flag
You need the actual row number (not just the row data) to update the correct cell

Full Working Code

// Modified to return both row data AND the row number for easier cell targeting
function getLastRowDetails(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var lastRowNumber = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  var lastRowData = sheet.getRange(lastRowNumber, 1, 1, lastCol).getValues()[0];
  
  return {
    data: lastRowData,
    rowNumber: lastRowNumber
  };
}

function submitToAPI() {
  var lastRowDetails = getLastRowDetails();
  var lastRowData = lastRowDetails.data;
  var lastRowNumber = lastRowDetails.rowNumber;
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Define which column to update (e.g., column 8 = column H; change this to your target column)
  const STATUS_COLUMN = 8;

  var data = {
    'name': lastRowData[2],
    'client': lastRowData[3],
    'starts_at': lastRowData[5],
    'ends_at': lastRowData[6],
    'project_state': lastRowData[4],
  };

  var payload = JSON.stringify(data);
  var options = {
    'method': 'POST',
    'contentType': 'application/json', // Note: Correct key is 'contentType' (lowercase 'c')
    'payload': payload, // Use the stringified payload here
    'muteHttpExceptions': true // This lets us handle errors without the script crashing
  };

  var url = 'https://api.10000ft.com/api/v1/projects?auth=token';
  var response;

  try {
    response = UrlFetchApp.fetch(url, options);
    var responseCode = response.getResponseCode();
    
    // Check if the request was successful (2xx status codes)
    if (responseCode >= 200 && responseCode < 300) {
      sheet.getRange(lastRowNumber, STATUS_COLUMN).setValue("true");
      Logger.log("Success! API response: " + response.getContentText());
    } else {
      sheet.getRange(lastRowNumber, STATUS_COLUMN).setValue("false");
      Logger.log("Request failed with status code: " + responseCode + ", response: " + response.getContentText());
    }
  } catch (error) {
    // Handle cases where the request couldn't even reach the API (e.g., network issues)
    sheet.getRange(lastRowNumber, STATUS_COLUMN).setValue("false");
    Logger.log("Request threw an error: " + error.message);
  }
}

Key Explanations

getLastRowDetails(): Returns both the row data and its row number, so we can easily target the exact cell to update later.
muteHttpExceptions: true: Prevents the script from crashing when the API returns an error status (like 400 Bad Request), letting us handle the error gracefully.
Status Code Check: Instead of relying on response.ok (which isn't a property of the UrlFetchApp response object), we check the HTTP status code directly—2xx codes mean success.
Target Column: Change STATUS_COLUMN to the column number you want to update (e.g., 1 for column A, 5 for column E).
Try-Catch Block: Catches any unexpected errors (like network timeouts) and sets the status to "false" in those cases too.