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<urls.length;i++) {
if (urls[i][0] != "") {
var lastrow = SpreadsheetApp.openById(urls[i][0]).getSheetByName("SheetformID").getLastRow();
var valuesToImport = SpreadsheetApp.openById(urls[i][0]).getSheetByName("Data").getRange(2,1,lastrow,30).getValues();
To:
var columns = ["A", "C", "D", "E", "F", "AC",,,]; // 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("SheetformID").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);
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<urls.length;i++) {
if (urls[i][0] != "") {
var lastrow = SpreadsheetApp.openById(urls[i][0]).getSheetByName("SheetformID").getLastRow();
var valuesToImport = SpreadsheetApp.openById(urls[i][0]).getSheetByName("Data").getRange(2,1,lastrow,30).getValues();
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<urls.length;i++) {
if (urls[i][0] != "") {
var ss = SpreadsheetApp.openById(urls[i][0]);
var lastrow = ss.getSheetByName("SheetformID").getLastRow();
var v = ss.getSheetByName("Data").getRange(2, 1, lastrow, 30).getValues();
var temp1 = v[0].map((_, c) => 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:
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<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);
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.
