AppScript.js

/**
 * Custom function to fetch the average price for an item by its TypeID using GESI's markets_prices function.
 * @param {number} typeID The TypeID of the item you want to look up.
 * @return {string} The average price of the item, or an error message if not found.
 * @customfunction
 */
function getAveragePrice(typeID) {
  try {
    // Fetch market prices using the markets_prices function for the given TypeID
    var result = GESI.invoke("markets_prices", { type_id: typeID });
    
    // Check if result data is available
    if (result && result.length > 0) {
      // Iterate through the result to find the matching TypeID and get the average price
      for (var i = 0; i < result.length; i++) {
        // The TypeID is in the third column (index 2)
        if (result[i][2] == typeID) {
          var averagePrice = result[i][1];  // The average price is in the second column (index 1)
          return averagePrice ? averagePrice : "Price not found";  
        }
      }
      return "TypeID not found in market data";  
    } else {
      return "No market data available";  
    }
  } catch (e) {
    return "Error: " + e.message;
  }
}

/**
 * Returns the minSell and MaxBuy price for the provided typeId at the provided locationID
 * 
 * @param {number} typeId to get privve data for
 * @param {number} locationID to filter on. Can either be a structre or a station ID.
 * @param {number} regionID to filter on. Only required if locationId is a station ID.
 * 
 * @customfunction
 */

/**
 * Fetches minimum sell price and maximum buy price for a given item and location.
 * @param {number} typeID - The TypeID of the item.
 * @param {number} locationID - The location ID to filter orders (structure or station).
 * @param {number} regionID - The region ID to fetch orders from if location is not a structure.
 * @return {Array} [minSell, MaxBuy] - Minimum sell price and maximum buy price.
 * https://www.youtube.com/watch?v=sRiLmEhsWW0
 */
function typePriceData(typeID, locationID, regionID) {
  var data = locationID > 10000000000000 
    ? GESI.invokeRaw("markets_structure_structure", { structure_id: locationID }) 
    : GESI.invokeRaw("markets_region_orders", { region_id: regionID, order_type: "all", type_id: typeID });

 
  var minSell = null;
  var maxBuy = null;

  // Process each order
  data.forEach(function(order) {
    if (order.type_id === typeID) {  
      if (order.is_buy_order) { /
        if (!maxBuy || order.price > maxBuy) {
          maxBuy = order.price; 
        }
      } else { // Sell order
        if (!minSell || order.price < minSell) {
          minSell = order.price; 
        }
      }
    }
  });

  // Return results
  return [[maxBuy, minSell]];
}

//menu function
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Time Stamp Menu')
      .addItem('Save Data','saveDataToSameSheet')
      .addToUi();
}
 
function saveDataToSameSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  
  try {
    //range A4:Q51
    const snapshotRange = sheet.getRange('A4:P51');
    const snapshotData = snapshotRange.getValues();
    
    const lastRow = sheet.getLastRow();
    
    // Add timestamp
    const timestamp = new Date().toLocaleString();
    sheet.getRange(lastRow + 2, 1).setValue('Snapshot: ' + timestamp);
    
    // Copy headers for new set of stuff
    const headers = sheet.getRange('A3:P3').getValues()[0];
    sheet.getRange(lastRow + 3, 1, 1, headers.length).setValues([headers]);
    
    // Paste all the data or it doesn't work because you forgot an index. And then forget a comma
    sheet.getRange(lastRow + 4, 1, snapshotData.length, snapshotData[0].length).setValues(snapshotData);
    
    SpreadsheetApp.getUi().alert('Snapshot saved');
    
  } catch (error) {
    SpreadsheetApp.getUi().alert('Error taking snapshot: ' + error.toString());
  }
}