How to paste a 2D array (with headers) into another sheet with column headers randomly set using GAS?

user_11832197 0
Viewed 79

The data:

[
 ["ID","Variação","Nome Site","Obs"],//Headers
 [11602,185,"Camisa Teste","Teste da Observação"] //To be pasted
]

This is how the destination is:

Obs Nome Site Variação ID

I understand I could take the approach below, but considering that the dataset has more then 40 columns, this is not that feasible:

var data = sheet.getRange(1,1,2,4).getValues();
var obs = data.map(function(e){return e[0];});
var NomeSite = data.map(function(e){return e[1];});
...

How would I go about finding the header and "pasting" the data in that case?

Thank you!

google-apps-script google-sheets javascript
2 Answers

I believe your goal is as follows.

  • You want to rearrange the values by your expected header.

In this case, how about the following modification?

Modified script:

var expectedHeader = ["Obs", "Nome Site", "Variação", "ID"]; // Please set your expected header.

// This sample value is from your question.
var data = [
  ["ID", "Variação", "Nome Site", "Obs"],
  [11602, 185, "Camisa Teste", "Teste da Observação"]
];

var [headers, ...rows] = data;
const obj = rows.map(r => headers.reduce((o, h, j) => (o[h] = r[j], o), {}));
var res = obj.map(o => expectedHeader.map(h => o[h] || ""));
console.log(res) // [["Teste da Observação","Camisa Teste",185,11602]]

  • When this script is used, [11602, 185, "Camisa Teste", "Teste da Observação"] is rearranged to ["Teste da Observação","Camisa Teste",185,11602].

References:

user_71086530

I like Tanaike, but I'm a little scared of reduce() function. So here is my variant without reduce():

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var header = sheet.getDataRange().getValues()[0];

  var data = [
    ["ID","Variação","Nome Site","Obs"],
    [11602,185,"Camisa Teste","Teste da Observação"]
  ]

  var indexes = header.map(x => data[0].indexOf(x));
  data = data.map(x => indexes.map(i => x.slice()[0] = x[i]));

  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

Output: enter image description here

user_142654690
Related