Apps Script to neat log JSON data to a table

Apps_Script_log_json.js

/*
Step 1: Create a new Google Sheets document.
Step 2: Open the script editor by going to "Extensions" -> "Apps Script" in the menu.
Step 3: In the script editor, delete any existing code and write the following code:
Step 4: Save the script by clicking on the floppy disk icon or using "File" -> "Save".
Step 5: Deploy the script as a web app by going to "Publish" -> "Deploy as web app" in the menu.
Configure the deployment settings as follows:
  Project version: New
  Execute the app as: Me
  Who has access to the app: Anyone, even anonymous
Step 6: Click on "Deploy" and authorize the script by following the prompts.
Step 7: Once deployed, you will receive a URL for your web app. This URL will be your API endpoint.

Now you can use the API endpoint to log requests with JSON data. 
You can send HTTP POST requests to the endpoint, and the doPost function in the script will handle the requests.
It will log the JSON data from the request into a new row in the "Sheet1" sheet of your Google Sheets document.
*/


function doPost(request) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var jsonData = JSON.parse(request.postData.contents);
  
  // Get the keys of the JSON object
  var keys = Object.keys(jsonData);
  
  // If the sheet is empty, add the keys as column headers
  if (sheet.getLastRow() == 0) {
    sheet.appendRow(keys);
    sheet.setFrozenRows(1);

    // Create an array to hold the row data
    var rowData = [];
    
    // Iterate over the keys and retrieve corresponding values from the JSON data
    for (var i = 0; i < keys.length; i++) {
      rowData.push(jsonData[keys[i]]);
    }
    
    // Append the row data to the sheet
    sheet.appendRow(rowData);
  }
  else {
    // Get the first row of the sheet
    var firstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    
    // Create a map to hold the existing column indexes for each key
    var columnIndexMap = {};
    
    // Iterate over the first row to populate the columnIndexMap
    for (var i = 0; i < firstRow.length; i++) {
      columnIndexMap[firstRow[i]] = i + 1;
    }
    
    // Iterate over the keys array to add missing values
    for (var i = 0; i < keys.length; i++) {
      var key = keys[i];
      
      // If the key is not present in the columnIndexMap, add it to the first row
      if (!columnIndexMap[key]) {
        var newColumnIndex = firstRow.length + 1;
        columnIndexMap[key] = newColumnIndex;
        sheet.getRange(1, newColumnIndex).setValue(key);
      }
    }

    // Create an array to hold the values for the new row
    var newRowValues = [];

    // Get the first row of the sheet
    var firstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    
    // Iterate over the column names in the first row
    for (var i = 0; i < firstRow.length; i++) {
      var columnName = firstRow[i];
      
      // Check if the column name exists in the JSON data
      if (jsonData.hasOwnProperty(columnName)) {
        // Push the value for the corresponding column name to the newRowValues array
        newRowValues.push(jsonData[columnName]);
      } else {
        // If the column name is missing in the JSON data, add a blank value
        newRowValues.push("");
      }
    }
    
    // Append the new row of values to the sheet
    sheet.appendRow(newRowValues);
  }
  
  return ContentService.createTextOutput('Request logged successfully');
}