How to get specific columns of data in a column range? Google Apps Script - Google Sheets

Question

I find a code :

function importRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Sheet1");
  var urlsSheet = ss.getSheetByName("sheet-have-ID");
  var urls = urlsSheet.getRange("rangeID").getValues();
  var j=1;
  for (var i=0;i


It works great, but getting all 30 columns is huge and unnecessary, I just want to get about 15 columns (A, C, D, E, F, AC,.....) in there. Is there any way to do that?

Tanaike · Accepted Answer

In your situation, how about the following patterns? In this answer, it supposes that urls is the spreadsheet ID. Please be careful this.

Pattern 1:

In this pattern, the columns of A, C, D, E, F, AC,..... are directly retrieved from the sheet "Data" using Sheets API and the retrieved values are put to the sheet sourceSheet. For this, please modify your script as follows. Before you use this script, please enable Sheets API at Advanced Google services.

From:

for (var i=0;i


To:
var columns = ["A", "C", "D", "E", "F", "AC",,,]; // Please set the column letters you want to retrieve.
for (var i=0;i `'${sheetName}'!${c}2:${c}${lastrow}`)}).valueRanges.map(({values}) => values.flat());
    var valuesToImport = v[0].map((_, c) => v.map(r => r[c]));
    SpreadsheetApp.flush(); // This might not be required to be used.
    var lastrowSource = sourceSheet.getLastRow();
    sourceSheet.getRange(lastrowSource + 1, 1, valuesToImport.length, valuesToImport[0].length).setValues(valuesToImport);

Pattern 2:
In this pattern, at first, all columns are retrieved. And then, the specific columns are retrieved using the column letters.
From:
for (var i=0;i

To:
// Ref: https://stackoverflow.com/a/21231012/7108653
const letterToColumn = letter => {
  let column = 0,
    length = letter.length;
  for (let i = 0; i < length; i++) {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
};

var columns = ["A", "C", "D", "E", "F", "AC",,,]; // Please set the column letters you want to retrieve.
var columnIndexes = columns.map(c => letterToColumn(c) - 1);
for (var i=0;i v.map(r => r[c]));
    var temp2 = columnIndexes.map(c => temp1[c]);
    var valuesToImport = temp2[0].map((_, c) => temp2.map(r => r[c]));
    SpreadsheetApp.flush(); // This might not be required to be used.
    var lastrowSource = sourceSheet.getLastRow();
    sourceSheet.getRange(lastrowSource + 1, 1, valuesToImport.length, valuesToImport[0].length).setValues(valuesToImport);

References:

Method: spreadsheets.values.batchGet
map()

Added:
When I saw your current script in your sample Spreadsheet, I found the following script.
Your current script:
function importRangeAPI() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("KH");
  var urlsSheet = ss.getSheetByName("ZZ");
  var urls = urlsSheet.getRange("B2:B20").getValues(); 
  
  var columns = ["A", "C", "D", "E", "F",,,,]; // Please set the column letters you want to retrieve.
for (var i=0;i `'${sheetName}'!${c}2:${c}${lastrow}`)}).valueRanges.map(({values}) => values.flat());
    var valuesToImport = v[0].map((_, c) => v.map(r => r[c]));
    SpreadsheetApp.flush(); // This might not be required to be used.
    var lastrowSource = sourceSheet.getLastRow();
    sourceSheet.getRange(lastrowSource + 1, 1, valuesToImport.length, valuesToImport[0].length).setValues(valuesToImport);
    Sheets.Spreadsheets.Values.batchGet()
}
}
}

In this case, please modify as follows.

Please modify var columns = ["A", "C", "D", "E", "F",,,,]; to var columns = ["A", "C", "D", "E", "F"];
Please remove Sheets.Spreadsheets.Values.batchGet().

When these are reflected to the script, it becomes as follows.
Modified script:
function importRangeAPI() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("KH");
  var urlsSheet = ss.getSheetByName("ZZ");
  var urls = urlsSheet.getRange("B2:B20").getValues();

  var columns = ["A", "C", "D", "E", "F"]; // Please set the column letters you want to retrieve.
  for (var i = 0; i < urls.length; i++) {
    if (urls[i][0] != "") {
      var ss = SpreadsheetApp.openById(urls[i][0]);
      var lastrow = ss.getSheetByName("Data").getLastRow();
      var sheetName = "Data";
      var v = Sheets.Spreadsheets.Values.batchGet(ss.getId(), { ranges: columns.map(c => `'${sheetName}'!${c}2:${c}${lastrow}`) }).valueRanges.map(({ values }) => values.flat());
      var valuesToImport = v[0].map((_, c) => v.map(r => r[c]));
      SpreadsheetApp.flush(); // This might not be required to be used.
      var lastrowSource = sourceSheet.getLastRow();
      sourceSheet.getRange(lastrowSource + 1, 1, valuesToImport.length, valuesToImport[0].length).setValues(valuesToImport);
    }
  }
}

Note:

From your question, unfortunately, I'm not sure about your actual Spreadsheet. Although I tested above script, no error occurs. But, when you tested above script using your actual Spreadsheet, can you provide the sample Spreadsheet for replicating your issue? By this, I would like to confirm it.

How to get specific columns of data in a column range? Google Apps Script - Google Sheets

Answers (1)

Pattern 1:

From:

To:

Pattern 2:

From:

To:

References:

Added:

Your current script:

Modified script:

Note:

Related Questions