Google scripts: Updating a column from a different tab only when numbers go up

user_16361214 0
Viewed 107

I'm attempting to create a database that will record the max price of an item in relation to an API for Black Desert Online. In the first column is the Index number of an item and the second column is the market price posted by this code into the MaxPrice Tab from the API Prices Tab:

function readSalesNum() {
var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Prices");
var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MaxPrice");

// Copy from 2nd row, 5th column, all rows for one column 
var index = sheetFrom.getRange(2, 2, sheetFrom.getLastRow(), 1).getValues();
var marketprice = sheetFrom.getRange(2, 5, sheetFrom.getLastRow(), 1).getValues();

//Item name overwrites itself
sheetTo.getRange(1,1,index.length,1).setValues(index);
sheetTo.getRange(1,2,index.length,1).setValues(marketprice);
}

I need to keep track of the max price all items sell for. The API is updated every 30 mins. In BDO there are market caps for all items enforced by the Devs. Which is why originally the script would post a new column with updated prices every 2 hours, and then =MAX to find the current highest price of the item. However if a new item is added to the API (in aphabetical order) it skews all the data

I need a script that will look at column A in MaxPrices, find the matching number in Prices then post the current market price to column B in MaxPrices. Then will only update column B in MaxPrices if the current market price in the Prices tab goes up for each item.

vlookup google-apps-script google-sheets
2 Answers

Hopefully the text of the commands are clear enough to just read and understand. I added slice(1) to the end of the getValues() to omit a header row.

  function myFunction() {
    var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MaxPrice");
    var data = sheetTo.getDataRange().getValues().slice(1);
    var maxPrices = Object.fromEntries(data.map(([id, price]) => [id, price]));
    
    var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Prices");
    data = sheetFrom.getDataRange().getValues().slice(1);
    
    data.forEach(row => {
      let id = row[1]; // column 2
      let price = row[4]; // column 5
      maxPrices[id] = maxPrices[id] === undefined ? price : Math.max( price, maxPrices[id])
    });
  
    var output = Object.entries(maxPrices).map(row => [...row]);
    sheetTo.getRange(2, 1, output.length, output[0].length).setValues(output);
  }
user_1739850

Ok so we need to create an existing list of max prices, compare/update them to the new prices, add new items.

function myFunction() {
  var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Prices"); 
  var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MaxPrice");

  //three steps

  //1. create dictionary of MaxPrices from MaxPrice sheet
  var data = sheetTo.getDataRange().getValues();
  var maxPriceData = {}
  for (var i=1; i<data.length;i++){maxPriceData[data[0]]=data[1]}

  //2. Compare current prices from column E of the Prices to the dictionary
  data = sheetFrom.getDataRange().getValues();
  for (var i=1; i<data.length;i++)
  {
    var itemId = data[i][1];//B
    var basePrice = data[i][4];//E
    if (itemId in maxPriceData ){
      if (basePrice > maxPriceData[itemId]) maxPriceData[itemId] = basePrice;//updates dictionary if new price higher
    }else{
      maxPriceData[itemId]=basePrice;//adds new item to the item list
    }
  }

  //3. Update MaxPrice spreadsheet
  var output = [];
  for (key in maxPriceData){
    var itemId = key;
    var price = maxPriceData[key];
    output.push([itemId,price]);
  }
  
  sheetTo.getRange(2, 1,output.length, output[0].length).setValues(output);

}

I haven't tested this against actual data so there might be a typo or two.

user_52973070
Related