CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user14656382
user14656382

Reputation: 13

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

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:

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!

Upvotes: 0

Views: 880

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15308

Try

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

Upvotes: 0

Tanaike
Tanaike

Reputation: 201378

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:

Upvotes: 1

Related Questions