使用AppScript调用Sage Accounting API提交数据时遇参数无效错误

问题描述 #

我正在尝试编写AppScript代码，通过Sage Accounting API向Sage会计软件中添加新客户。Postman测试接口正常，但AppScript代码运行报错。

原代码 #

function postDataToSageOneAPI() {
  // Define your API endpoint URL
  var apiUrl = 'https://resellers.accounting.sageone.co.za/api/2.0.0/Customer/Save';

  // Define  authentication credentials
  var username = 'myusername';
  var password = 'mypassword';

  // Extract the CompanyId from the URL
  var companyId = "14787";

  // Define the API key
  var apiKey = "{####################################}";

  // Create the payload data you want to send as JSON
  var payload = '{"Name": "string1", "Email": "string2"}'; // Assuming this is a valid JSON string

  // Add the API key and CompanyId to the URL as query parameters
  apiUrl += '?apikey=' + apiKey + '&CompanyId=' + companyId;

  // Define the headers for the request
  var headers = {
    'Authorization': 'Basic ' + Utilities.base64Encode(username + ':' + password),
    'Content-Type': 'application/json',
  };

  // Configure the options for the request
  var options = {
    'method': 'POST',
    'headers': headers,
    'payload': payload,
  };

  // Make the POST request to the Sage One API
  var response = UrlFetchApp.fetch(apiUrl, options);

  // Check the response status code
  var statusCode = response.getResponseCode();

  // Handle the response
  if (statusCode === 200) {
    var responseBody = response.getContentText();
    // Process the responseBody as needed
    Logger.log('Response Body: ' + responseBody);
  } else {
    // Handle errors or other status codes
    Logger.log('Error: ' + statusCode);
  }
}

报错信息 #

Exception: Invalid argument:

https://resellers.accounting.sageone.co.za/api/2.0.0/Customer/Save?apikey=%7B#####################%7D&CompanyId=14787

postDataToSageOneAPI @ Code.gs:35

问题原因及解决方案 #

1. URL参数未正确编码 #

你的apiKey包含{}这类URL特殊字符，直接拼接进URL会导致请求地址无效。Postman会自动对URL参数进行编码，但AppScript的字符串拼接不会处理这一步。需要用encodeURIComponent()对参数值进行编码。

2. （可选优化）Payload用对象代替字符串 #

手动拼接JSON字符串容易出现格式错误，AppScript支持直接传入JavaScript对象作为payload，会自动转换为JSON格式，更可靠。

修正后的代码 #

function postDataToSageOneAPI() {
  var apiUrl = 'https://resellers.accounting.sageone.co.za/api/2.0.0/Customer/Save';

  var username = 'myusername';
  var password = 'mypassword';
  var companyId = "14787";
  var apiKey = "{####################################}";

  // 用对象定义payload，避免手动拼接JSON
  var payload = {
    "Name": "string1",
    "Email": "string2"
  };

  // 对URL参数进行编码
  var encodedApiKey = encodeURIComponent(apiKey);
  var encodedCompanyId = encodeURIComponent(companyId);
  apiUrl += '?apikey=' + encodedApiKey + '&CompanyId=' + encodedCompanyId;

  var headers = {
    'Authorization': 'Basic ' + Utilities.base64Encode(username + ':' + password),
    'Content-Type': 'application/json',
  };

  var options = {
    'method': 'POST',
    'headers': headers,
    'payload': JSON.stringify(payload), // 将对象转为JSON字符串
    'muteHttpExceptions': true // 可选：开启后可以获取详细错误响应，方便调试
  };

  try {
    var response = UrlFetchApp.fetch(apiUrl, options);
    var statusCode = response.getResponseCode();

    if (statusCode === 200) {
      var responseBody = response.getContentText();
      Logger.log('Response Body: ' + responseBody);
    } else {
      Logger.log('Error Status: ' + statusCode);
      Logger.log('Error Response: ' + response.getContentText());
    }
  } catch (e) {
    Logger.log('Request Error: ' + e.toString());
  }
}

额外调试建议 #

• 开启muteHttpExceptions: true可以获取API返回的详细错误信息，帮助进一步排查问题。
• 对比Postman的请求URL和AppScript生成的URL，确保参数编码一致。

内容的提问来源于stack exchange，提问作者Abdul Hamid Vorajee