Get API Responses Directly in Google Sheets Like Postman

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('OutrightStore')
    .addItem('Show Sidebar', 'showSidebar')
    .addToUi();
}

function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar3')
    .setTitle('Sidebar Form')
    .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}

function submitRequest(url, method, email) {
  saveEndpoint(url, method);
  try {
    if (method.toUpperCase() === "GET") {
      var response = fetchDataFromEndpoint(url);
    } else {
      var response = convertSheetDataToJsonAndSend(url, method);
    }
    sendEmail(email, response);
    return response;
  } catch (e) {
    Logger.log("Error in submitRequest: " + e);
    return "Error: " + e.toString();
  }
}

function sendEmail(email, content) {
  var subject = "Data from Sheet2";
  MailApp.sendEmail(email, subject, content);
}

function saveEndpoint(url, method) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet3 = ss.getSheetByName("Sheet2");
  if (!sheet3) {
    sheet3 = ss.insertSheet("Sheet2");
    sheet3.getRange("A1").setValue("Endpoint");
    sheet3.getRange("B1").setValue("Method");
  }
  
  // Overwrite first data row instead of inserting a new row
  sheet3.getRange("A2").setValue(url);
  sheet3.getRange("B2").setValue(method);
}

function convertSheetDataToJsonAndSend(url, method) {
  var endpointUrl = url || getEndpointUrl();
  if (!endpointUrl) {
    SpreadsheetApp.getUi().alert('Endpoint URL not found. Please enter the endpoint URL.');
    return;
  }

  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var dataRange = sheet1.getDataRange();
  var data = dataRange.getValues();
  var headers = data[0];
  var jsonData = [];

  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var jsonObject = {};
    for (var j = 0; j < headers.length; j++) {
      jsonObject[headers[j]] = row[j];
    }
    jsonData.push(jsonObject);
  }

  var jsonString = JSON.stringify(jsonData, null, 2);

  try {
    var options = {
      "method": method,
      "contentType": "application/json",
      "payload": jsonString
    };
    var response = UrlFetchApp.fetch(endpointUrl, options);
    showResponseToUser(response);
    return response.getContentText();
  } catch (e) {
    Logger.log("Error sending data to endpoint: " + e);
    return "Error: " + e.toString();
  }
}

function showResponseToUser(response) {
  Logger.log(response.getContentText ? response.getContentText() : response);
}

function getEndpointUrl() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet3 = ss.getSheetByName("Sheet2");
  return sheet3 ? sheet3.getRange("A2").getValue() : null;
}

function fetchDataFromEndpoint(url) {
  try {
    var response = UrlFetchApp.fetch(url);
    return response.getContentText();
  } catch (e) {
    Logger.log("Error fetching data from endpoint: " + e);
    return "Error: " + e.toString();
  }
}