AppSScript Code

Copy 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();
}