How can I copy values from specific columns by google app script-CodePudding

I need to get & combine values of some specific columns (Name, Age, Phone) from a raw sheet to another sheet but I don't know how to do it with the google app script.

the workflow looks like as below:

First, I need to get values of only Name, Age, Phone columns from the raw sheet, I don't want to collect the region column. The raw sheet looks like as an image here:

Then I think I have to push it to an array and paste it to the result sheet which contains 3 columns: Name, Age, Phone

In query function I can do it with this statement:

=query("raw sheet","select Col1, Col3, Col4")

but I don't know how to do it with google app script.

If you use to try it with google app script, please share with me the reference code to do it, thank you so much!

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the specific columns from a source sheet, and want to put the retrieved columns to the destination sheet using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script and set the variables of sourceAndDestinationSheet and dstHeader and run the function with the script editor.

function myFunction() {
  const sourceAndDestinationSheet = ["source sheet name", "destination sheet name"]; // Please set the source sheet name and destination sheet name to the 1st and 2nd element.
  const dstHeader = ["Name", "age", "phone"]; // This is the header of the destination values.

  // 1. Retrieve values from the source sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = sourceAndDestinationSheet.map(s => ss.getSheetByName(s));
  const [header, ...values] = srcSheet.getDataRange().getValues();

  // 2. Create an array of destination values.
  const colIndexes = dstHeader.map(h => header.indexOf(h));
  const dstValues = [dstHeader, ...values.map(r => colIndexes.map(i => r[i]))];

  // 3. Put the destination values to the destination sheet.
  dstSheet.getRange(1, 1, dstValues.length, dstValues[0].length).setValues(dstValues);
}

Note:

If you want to use this script as a custom function, how about the following script? In this case, please put a custom formula of =SAMPLE(Sheet1!A1:D, "Name,age,phone") (It supposes that the source sheet and range are "Sheet1" and "A1:D") to the destination sheet.
```
  function SAMPLE(v, h) {
    const dstHeader = h.split(",").map(e => e.trim());
    const [header, ...values] = v;
    const colIndexes = dstHeader.map(h => header.indexOf(h));
    return [dstHeader, ...values.map(r => colIndexes.map(i => r[i]))];
  }
```
In this sample script, it is required to be the same between dstHeader and the actual header name of the source sheet. Please be careful this.

References:

CodePudding user response：

Try

=query('raw_2'!A1:D,"select A,B,D where A is not null",1)