-1

I've got an array question for processing in google sheets. I'm trying to map a value from one sheet into another. Thanks for any help. I've got explanation and code below...

PROBLEM: this gives me blank rows (policies2[i]).

DESIRED OUTCOME: new values from entry_top in the last row of policies2.

I'm working with two sheets. policies.values is the array from a normal google sheet of tabular values. policies2 is a copy of policies. entry_top is the array of values from a sheet with unstructured data, except that headers and values are beside each other.

So if "Line" is a header in policies, it would find "Line" in a cell/array node in entry_top and then get the next cell/array value (next column/index/to the right), take that value and put it in the last row in the same column in policies (match by header).

So the code below loops through policies.values[0], the header values. Then loops through every cell in entry_top, by rows (e) and columns (ee). Then if the headers match (h and ee), it puts the next value ([ei][eei+1]) in the matching policies2 column in the last row ([policies2.length-1][hi]).

policies.values[0].map((h, hi) => {
  entry_top.map((e, ei) => {
    e.map((ee, eei) => {
      if (ee == h) policies2[policies2.length - 1][hi] = entry_top[ei][eei + 1];
    });
  });
});

MRE: oddly this example works. so not sure what above is causing an issue...

function testdp() {

  policies = {
    values:[[1,2,3],[4,5,6],[7,8,9]]
  }
  policies2=[[1,2,3],[4,5,6],[7,8,9],[,,,]];
  entry_top = [[,,1,'add',,],[,'a','b',2,'add2',,'c'],['c',,,3,'add3',,]]

  Logger.log(policies.values);
  Logger.log(policies2);
  Logger.log(entry_top);
  policies.values[0].forEach((h,hi)=>{
    entry_top.forEach((e,ei)=>{
      e.forEach((ee,eei)=>{
      if (ee == h) policies2[policies2.length - 1][hi] = entry_top[ei][eei + 1];
      })
    })   
  });
  
  Logger.log(policies2);
// [[1.0, 2.0, 3.0], [4.0, 5.0, 6.0], [7.0, 8.0, 9.0], [add, add2, add3]]

}
Jason Torpy
  • 124
  • 14
  • Are you sure `policies2` is a copy, not a reference to the same array? – Barmar Apr 08 '22 at 15:40
  • FYI, you should use `forEach()` instead of `map()` if you're not using the results. – Barmar Apr 08 '22 at 15:43
  • Please create a [mre] that includes sample values of all the arrays. – Barmar Apr 08 '22 at 15:44
  • thanks for the formatting. I'll keep in mind next time the need to format variable names as code. the reproducible example is very difficult to create in most cases. you added some whitespace but do you see anything else necessarily wrong with this? What do you think it would do? – Jason Torpy Apr 08 '22 at 16:04
  • also I did try with forEach. I understand map will return the array unnecessarily. properities2 is a new array copy, not a reference. appendRows is a custom function that adds a blank row (in this case). it is working as expected. let properties2 = appendRows(properties.values); – Jason Torpy Apr 08 '22 at 16:06
  • I'm really having a hard time understanding what it's trying to do, without seeing sample input and desired output. – Barmar Apr 08 '22 at 16:11
  • map returns a new Array of values that match the criteria so I would image there would be a lot of undefines since you are using explicit indexes [hi] and I don't see you assigning `policies.values[0].map((h, hi) => {` to a variable. – TheWizEd Apr 08 '22 at 16:36
  • I think what you are trying to do is match an array of key value pairs to a typical spreadsheet array of columns with headers? And put the value at the end of the column that matches? – TheWizEd Apr 08 '22 at 16:47
  • yes. correct on both points, though undefined would be ok but there won't be as that is handled separately. @TheWizEd – Jason Torpy Apr 08 '22 at 16:52
  • also @Barmar I did an example manually and it worked like a champ, so now I'm really confused. I'll edit above. – Jason Torpy Apr 08 '22 at 16:52
  • @TheWizEd He's basically using `map()` as `forEach()`, so the `undefined` values are irrelevant. – Barmar Apr 08 '22 at 16:57
  • If it works in that example, I suspect the problem is with how you're converting the spreadsheet to arrays. – Barmar Apr 08 '22 at 16:58
  • @Barmar - I'd say that too spreadsheet conversion is used a bunch elsewhere. this is just a new bolt-on module. I'm reviewing the logs to check that too. (They're let also so they should be able to change.) but anyway, you're probably right it has to be with something in the area. – Jason Torpy Apr 08 '22 at 17:02

1 Answers1

-1

this all works just fine. I was logging the wrong result.

Jason Torpy
  • 124
  • 14