0
149
ViewsHow to paste a 2D array (with headers) into another sheet with column headers randomly set using GAS?
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!
·
Juan Pablo Isaza
2 answers
Answer question0
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:
·
Juan Pablo Isaza
Report
0
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:
·
Juan Pablo Isaza
Report
Answer question