Compare objects in array to rows on sheet

Question

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;

User name
User ID
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 2856 50 40
Bob 2957 20
Peter 4579 80

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

Can you provide the code you're working on, and clarify what you mean by I want to list the last 30 days of value 3? Does this mean you are retrieving the array daily, and want to update the sheet daily? If that's not the case, how you identify the different days for each User? — Iamblichus
– Iamblichus, Commented Sep 28, 2020 at 12:49
@TheMaster What I understand is the value which is currently in the sheet gets shifted right. — Iamblichus
– Iamblichus, Commented Sep 28, 2020 at 14:50
@Iamblichus Thank you for the clarification. I feel you are right and rollbacked my previous edit. — TheMaster
– TheMaster, Commented Sep 28, 2020 at 14:53

TheMaster · Accepted Answer · 2020-09-28 14:59:34Z

1

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>

edited Sep 28, 2020 at 14:59

answered Sep 28, 2020 at 14:42

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare objects in array to rows on sheet

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related