You can get API responses directly in Google Sheets using IMPORTDATA, IMPORTXML, or custom scripts via Apps Script. This works like Postman by sending requests and retrieving JSON or XML data. It’s perfect for live data integration, automating tasks, and visualizing responses—no external tools needed!.

Step 1: Click on the Copy button to copy the code snippet.
Step 2: Paste the copied code into your project’s script editor.

Apps Scripts Blog

Apps Scripts Code

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('OutrightStore')
      .addItem('Show Sidebar', 'showSidebar')
      .addToUi();
 
  // Call your function here if you want it to run automatically on open
  showSidebar();
}


// Function to create and display a custom sidebar
function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar3')
      .setTitle('Sidebar Form')
      .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}


function submitRequest(url, method, email) {
  saveEndpoint(url, method);
  if (method === "GET") {
    var response = fetchDataFromEndpoint(url);
    sendEmail(email, response); // Send the fetched data to the provided email
    return response;
  } else {
    var response = convertSheetDataToJsonAndSend(url, method);
    sendEmail(email, response); // Send the converted data to the provided email
    return response;
  }
}


function sendEmail(email, content) {
  // Send an email with the content to the provided email address
  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");
    // Add headers if the sheet is newly created
    // sheet3.getRange("A1").setValue("Endpoint");
    // sheet3.getRange("B1").setValue("Method");
  }


  // Insert a new row at the second row (index 2) to push existing data downwards
  sheet3.insertRowBefore(1);
 
  // Set the values of the new row with the latest data
  sheet3.getRange("A1").setValue(url);
  sheet3.getRange("B1").setValue(method);
}








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


  var sheet1 = ss.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);


  var options = {
    "method": method,
    "contentType": "application/json",
    "payload": jsonString
  };
 
  var response = UrlFetchApp.fetch(endpointUrl, options);
  showResponseToUser(response);
  return response.getContentText(); // Return the response content
}


function showResponseToUser(response) {
 Logger.log(response);
}






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


function fetchDataFromEndpoint(url) {
  var response = UrlFetchApp.fetch(url);
  return response.getContentText();
}

HTML Code

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    body {
      font-family: Arial, sans-serif;
      background-color: #f4f4f4;
      margin: 0;
      padding: 0;
    }
    .container {
      max-width: 500px;
      height: 500px;
      margin: 0px auto;
      padding: 20px;
      background-color: #fff;
      border-radius: 5px;
      box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
    }
    h2 {
      text-align: center;
      color: #333;
    }
    label {
      font-weight: bold;
    }
    select,
    input[type="text"],
    input[type="button"],
    input[type="email"] {
      width: 100%;
      padding: 10px;
      margin: 5px 0 15px 0;
      border: 1px solid #ccc;
      border-radius: 3px;
      box-sizing: border-box;
      font-size: 16px;
    }
    input[type="button"] {
      background-color: #4CAF50;
      color: white;
      border: none;
      cursor: pointer;
    }
    input[type="button"]:hover {
      background-color: #45a049;
    }
  </style>
</head>
<body>
  <div class="container">
    <h2>Test Endpoint</h2>
    <form id="endpointForm">
      <label for="method">Method:</label><br>
      <select id="method" name="method">
        <option value="GET">GET</option>
        <option value="POST">POST</option>
        <option value="PUT">PUT</option>
        <option value="PATCH">PATCH</option>
      </select><br><br>
      <label for="endpointUrl">Endpoint Credentials:</label><br>
      <input type="text" id="endpointUrl" name="endpointUrl" placeholder="Enter endpoint URL"><br><br>
      <label for="email">Email Address:</label><br>
      <input type="email" id="email" name="email" placeholder="Enter email address"><br><br>
      <input type="button" value="Submit" onclick="submitRequest()">
    </form>
  </div>
  <script>
    function submitRequest() {
      var method = document.getElementById("method").value;
      var url = document.getElementById("endpointUrl").value;
      var email = document.getElementById("email").value;
      google.script.run.withSuccessHandler(showResponseToUser).submitRequest(url, method, email);
    }
    function showResponseToUser(response) {
      alert("Response from server:\n" + response);
    }
  </script>
</body>
</html>

Get API Responses Directly in Google Sheets Like Postman

Get API Responses Directly in Google Sheets Like Postman

Apps Scripts Blog

Apps Scripts Code

HTML Code