How to print data from 2d list to spreadsheet but only selected column?-CodePudding

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.

CodePudding user response：

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);
}

CodePudding user response：

In your situation, how about the following sample script?

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);
}

I think that the value of data can be also retrieved from the Spreadsheet.

Reference:

map()

CodePudding user response：

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();
}