CODEWITHSUNDEEP
arraysgoogle-apps-script
Richy Caldwell
Richy Caldwell

Reputation: 13

Compare objects in array to rows on sheet

first time posting on here so be gentle! Haha.

I'm more than happy to post my current code if my explanation doesn't meet everyones standards.

I have an array which I have ripped data from an API stored in it. Each object in the array contains 3 values;

  1. User name
  2. User ID
  3. Current statistic

Each row on my sheet contains these 3 properties and I want to list the last 30 days of value 3.

So on the first day, a row may contain A1 "John" B1 "12345" C1 "5"

The 2nd day it would contain A1 "John" B1 "12345" C1 "20" D1 "5"

3rd day A1 "John" B1 "12345" C1 "40" D1 "20" E1 "5"

I can do this even with my limited knowledge of loops, what I have issues with is if there is a new entry in the array that is NOT on the sheet I need to be able to add it. Then I can continue adding data every day from there.

Thanks in advance.

  function GymStrength1() {

  var ss = SpreadsheetApp.openById("1bXc_AZcAAl09bf0ibfC6vQSY9O3ikpn0Ru7pZ4oZ0g8");
  var sheet = ss.getSheetByName("Strength");
  var currentData = sheet.getDataRange().getValues();

  var response = UrlFetchApp.fetch("https://api.torn.com/faction/?selections=basic,contributors&stat=gymstrength&key=NhNhNbCmgGaqx0vQ");
  var json = response.getContentText();
  var data = JSON.parse(json);

  var totalMembers = data["contributors"]["gymstrength"];
  var activeMembers = [];
  var newData = [];

  // Gets active members and their daily contribution

  for (var obj in totalMembers){

  var playerID = obj;
  var currentStat = data["contributors"]["gymstrength"][obj]["contributed"];
  var currentMember = data["contributors"]["gymstrength"][obj]["in_faction"];

  if (currentMember == "1"){
  activeMembers.push([data["members"][obj]["name"],playerID,currentStat])
  }
  }

  // for each active member, check against sheet first
  // if row contains member, then merge row with member and paste into newData
  // if row doesnt contain member then add member into newData

  var copy = false;
  for (var member in activeMembers){

  for (var i = 1; i < currentData.length; i++){
  var row = currentData[i];

  // updates each row. works fine

  if (activeMembers[member][1] == row[2]){
  newData.push([activeMembers[member][0],activeMembers[member][1],activeMembers[member][2],row[3],row[4]]);
  copy = true;
  }


  }
  if (!copy){
  newData.push([activeMembers[member][0],activeMembers[member][1],activeMembers[member][2],"0","0"]);
  }
  }


  console.log(activeMembers)
  console.log(newData)


    sheet.getRange(2, 2, currentData.length, currentData[0].length).clearContent();
    sheet.getRange(2, 2, newData.length, newData[0].length).setValues(newData);
  }

I'm basing the comparison of the newData array and the sheet on the currentStat value of each entry. If the currentStat of an object in activeMembers is NOT on the sheet, I want the name, playerID and currentStat pushed to the newData array. If the currentStat IS on the sheet, it pushes the name, playerID and currentStat to the newData array as well as copying the rest of the values on that row. I can do that.

I am having trouble with adding entries that are NOT on the sheet, updating them works fine.

Brief example:

activeMembers array has obj name, playerID, currentStat

[
 ['John', '2856', '50'], 
 ['Bob', '2957', '20'],
 ['Peter', '4579', '80']
]

sheet currently only has

[['John', '2856', '40']]

I would like the end result on the sheet to be

John's 40 value has shifted to the right and the current value put in its place.

Upvotes: 0

Views: 68

Answers (1)

TheMaster
TheMaster

Reputation: 50697

  • Create a map of sheet data: id=>oldStat
  • Modify the activeMembers array in place using the map data

/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/
const activeMembers = [
  ['John', '2856', '50'],
  ['Bob', '2957', '20'],
  ['Peter', '4579', '80'],
];
const sheetData = [['John', '2856', '40']];
const sMap = new Map();
sheetData.forEach(([, id, oldStat]) => sMap.set(id, oldStat));
activeMembers.forEach(row =>
  row.push(sMap.has(row[1]) ? sMap.get(row[1]) : '')
);
console.info({sMap,activeMembers});
console.log(JSON.stringify(activeMembers));
<!-- https://meta.stackoverflow.com/a/375985/ -->    <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>

Upvotes: 1

Related Questions