How to make cross-domain HTTP POST requests from Google Sheets

Other Questions Google docs
1

I’m trying to set up an automatic POST request from Google Sheets to another website. The goal is to trigger the request whenever a cell changes. The request should include data from various cells in the sheet. I’ve set up the basic structure but I’m having trouble actually sending the request. Here’s what I’ve got so far:

function cellChanged(event) {
  let spreadsheet = SpreadsheetApp.getActive();
  let firstSheet = spreadsheet.getSheets()[0];
  let changedRange = event.range;

  let statuses = {
    pending: 'pending',
    accepted: 'accepted',
    rejected: 'rejected'
  };

  let orderCost = 1000;
  let secretKey = 'b2btxyz9';
  let customerCode = firstSheet.getRange(changedRange.getRow(), 9).getValue();
  let orderNumber = firstSheet.getRange(changedRange.getRow(), 2).getValue();

  let newCellValue = event.value;
  let apiEndpoint = 'https://myapi.example.com/update?customer=' + customerCode + '&price=' + orderCost + '&order=' + orderNumber + '&key=' + secretKey + '&state=';

  if (['new', 'processing', 'shipped'].includes(newCellValue)) {
    // Need to send POST/GET with pending status
  } else if (['approved', 'shipped', 'delivered'].includes(newCellValue)) {
    changedRange.setNote('accepted');
    // Need to send POST/GET with accepted status
  } else if (['declined', 'returned'].includes(newCellValue)) {
    changedRange.setNote('rejected');
    // Need to send POST/GET with rejected status
  }
}

I’ve tried using UrlFetchApp.fetch() and XMLHttpRequest, but neither worked. Any ideas on how to make this cross-domain request work?

2

I’ve encountered similar challenges with Google Sheets and API integration. One crucial aspect you might be overlooking is error handling. It’s essential when dealing with network requests. Here’s a suggestion to improve your code:

function sendRequest(endpoint, status) {
  try {
    let response = UrlFetchApp.fetch(endpoint + status, {
      'method': 'post',
      'muteHttpExceptions': true,
      'payload': JSON.stringify({
        status: status,
        timestamp: new Date().toISOString()
      })
    });
    
    if (response.getResponseCode() == 200) {
      Logger.log('Request successful');
    } else {
      Logger.log('Request failed: ' + response.getContentText());
    }
  } catch (error) {
    Logger.log('Error: ' + error.toString());
  }
}

Implement this function in your existing code for each status change. This approach provides better error handling and logging, which can be crucial for debugging cross-domain issues. Remember to check your API’s documentation for any specific headers or authentication requirements.

3

hey mate, i think i can help. try using UrlFetchApp.fetch() like this:

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

this should work for cross-domain requests. lemme know if u need more help!

4

I’ve dealt with a similar situation before, and I can share what worked for me. The key is to use UrlFetchApp.fetch() correctly. Here’s how you can modify your code:

if (['new', 'processing', 'shipped'].includes(newCellValue)) {
  let response = UrlFetchApp.fetch(apiEndpoint + statuses.pending, {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify({
      'customer': customerCode,
      'price': orderCost,
      'order': orderNumber
    })
  });
  // Handle response as needed
}

You’ll need to add similar blocks for the other conditions. Make sure your API endpoint is set up to receive POST requests and that you’re sending the data in the format it expects.

Also, remember that Google Sheets scripts run server-side, so you don’t need to worry about cross-origin issues. If you’re still having trouble, double-check your API endpoint and ensure you have the necessary permissions set up in your Google Cloud Console.