I know this is very basic knowledge, even after I searched for the answers in here, I still haven't got the correct answers yet. I would like to print 2D list data into a spreadsheet, however only the selected column.
Here's the sample of the data from the 2dlist:
| Name | Age | Gender | Country |
|---|---|---|---|
| Annie | 26 | Female | A |
| John | 22 | Male | B |
| Katie | 24 | Female | C |
| Ronaldo | 26 | Male | D |
However, I only need to print the first two column into spreadsheet which is Name and Age. What script should I use?
Thank you, I really appreciate the help.
Given your table, your 2D array looks like this:
const data = [
['Name', 'Age', 'Gender', 'Country'],
['Annie', 26, 'Female', 'A'],
['John', 22, 'Male', 'B'],
['Katie', 24, 'Female', 'C'],
['Ronaldo', 26, 'Male', 'D'],
];
The snippet below will return a new 2D array with the first 2 columns only:
const data = [
['Name', 'Age', 'Gender', 'Country'],
['Annie', 26, 'Female', 'A'],
['John', 22, 'Male', 'B'],
['Katie', 24, 'Female', 'C'],
['Ronaldo', 26, 'Male', 'D'],
];
const getFirstTwoCols = data => data.map(row => [row[0], row[1]]);
console.log(getFirstTwoCols(data));Then you can paste the new data where you like like so, for example:
const pasteData = () => {
const newData = getFirstTwoCols(data)
SpreadsheetApp
.getActive()
.getSheetByName('Sheet2')
.getRange(1, 1, newData.length, newData[0].length)
.setValues(newData);
}
In your situation, how about the following sample script?
function myFunction() {
// This is your 2D list.
const data = [["Name", "Age", "Gender", "Country"],
["Annie", "26", "Female", "A"],
["John", "22", "Male", "B"],
["Katie", "24", "Female", "C"],
["Ronaldo", "26", "Male", "D"]];
const selected = ["Name", "Age"]; // This is your selected columns.
// 1. Transpose the 2D list.
const transpose = data[0].map((_, c) => data.map(r => r[c]));
// 2. Retrieve the selected columns.
const cols = transpose.reduce((ar, c) => {
if (selected.includes(c[0])) ar.push(c);
return ar;
}, []);
// 3. Transpose the selected columns.
const values = cols[0].map((_, c) => cols.map(r => r[c]));
// 4. Put the values to the Spreadsheet.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set the sheet name.
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
data can be also retrieved from the Spreadsheet.function myfunk() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet0');//I pasted your list into spreadsheet
const list = sh.getDataRange().getValues();
const osh = ss.getSheetByName('Sheet1');
let oA = list.map(r => [r[0],r[1]]);
osh.getRange(1,1,oA.length,oA[0].length).setValues(oA);
SpreadsheetApp.flush();
}