Sending HTTP-POST requests across domains from Google Sheets

Other Questions Google docs
1

I’m trying to set up a system where changes in my Google Sheet trigger POST requests to another website. The idea is to send data from different cells whenever there’s an edit. I’ve got the basic structure down, but I’m stuck on actually sending the request. Here’s what I’ve tried so far:

function onEdit(e) {
  let spreadsheet = SpreadsheetApp.getActive();
  let activeSheet = spreadsheet.getActiveSheet();
  let editedCell = e.range;

  let statusOptions = {
    pending: 'pending',
    confirmed: 'confirmed',
    rejected: 'rejected'
  };

  let paymentAmount = 500;
  let securityToken = 'b2c3d4e5';
  let userCode = activeSheet.getRange(editedCell.getRow(), 5).getValue();
  let transactionID = activeSheet.getRange(editedCell.getRow(), 3).getValue();

  let cellValue = e.value;
  let apiEndpoint = 'https://api.example.com/update?user=' + userCode + '&amount=' + paymentAmount + '&transaction=' + transactionID + '&token=' + securityToken + '&status=';

  if (['new', 'processing', 'sent'].includes(cellValue)) {
    // Need to send request with 'pending' status
  } else if (['confirmed', 'completed'].includes(cellValue)) {
    editedCell.setNote('confirmed');
    // Need to send request with 'confirmed' status
  } else if (['rejected', 'cancelled'].includes(cellValue)) {
    editedCell.setNote('rejected');
    // Need to send request with 'rejected' status
  }
}

Any ideas on how to actually send these POST requests?

2

I’ve dealt with a similar situation, and I can tell you it’s not as straightforward as it seems. One thing to keep in mind is that Google Sheets has a quota limit for external requests, so you might want to implement some error handling and possibly a backoff mechanism.

For the actual POST request, you’re on the right track with UrlFetchApp.fetch(). However, I’d suggest structuring your data a bit differently. Instead of building the URL with query parameters, consider sending a JSON payload. It’s generally more flexible and secure.

Here’s a rough idea of how you could modify your code:

let payload = {
  user: userCode,
  amount: paymentAmount,
  transaction: transactionID,
  token: securityToken,
  status: statusOptions[cellValue] || 'pending'
};

let options = {
  'method': 'post',
  'contentType': 'application/json',
  'payload': JSON.stringify(payload)
};

try {
  let response = UrlFetchApp.fetch(apiEndpoint, options);
  // Handle response here
} catch(e) {
  // Error handling
}

This approach gives you more control over the data you’re sending and how it’s formatted. Just remember to adjust your API endpoint to expect a JSON payload instead of query parameters.

3

hey silentsailing34, i think you’re close! for sending POST requests, try using UrlFetchApp.fetch(). something like:

let options = {
  'method': 'post',
  'payload': JSON.stringify(yourData)
};
UrlFetchApp.fetch(apiEndpoint, options);

good luck with your project!

4

I’ve encountered similar challenges with cross-domain POST requests from Google Sheets. One crucial aspect you might want to consider is CORS (Cross-Origin Resource Sharing) restrictions. Your target API needs to allow requests from Google’s domain.

For the actual implementation, you’re on the right track. I’d suggest using UrlFetchApp.fetch() as others have mentioned, but also consider adding some error handling and logging. Here’s a snippet that might help:

function sendPostRequest(status) {
  let payload = {
    user: userCode,
    amount: paymentAmount,
    transaction: transactionID,
    token: securityToken,
    status: status
  };

  let options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload),
    'muteHttpExceptions': true
  };

  try {
    let response = UrlFetchApp.fetch(apiEndpoint, options);
    let responseCode = response.getResponseCode();
    if (responseCode === 200) {
      Logger.log('Request successful');
    } else {
      Logger.log('Request failed with code ' + responseCode);
    }
  } catch(e) {
    Logger.log('Error: ' + e.toString());
  }
}

Incorporate this function into your onEdit trigger, calling it with the appropriate status based on the cell value. Remember to test thoroughly and monitor your quota usage.