I believe your goal as follows.
- You want to retrieve CSV data from an URL.
- You want to put the CSV data to Google Spreadsheet by retrieving the specific columns.
- You want to achieve this using Google Apps Script.
- When I saw the URL of
https://bionicteaching.com/importing-csv-into-google-sheets-via-google-script/comment-page-1/ in your question, I understood that the script is Google Apps Script.
- You are using the script of
https://bionicteaching.com/importing-csv-into-google-sheets-via-google-script/comment-page-1/.
Modification points:
- In the current stage,
Utilities.parseCsv() can be used for parsing the CSV data as an array. When this method is used, the CSV data can be parsed as 2 dimensional array. I thought that this might be able to be used.
- In order to retrieve the specific columns, I thought that it can be retrieved from the array parsed from the CSV data.
When above points are reflected to the script, it becomes as follows.
Sample script:
Please copy and paste the following script to the script editor of Google Spreadsheet. And, please set the variables, and run myFunction. By this, the CSV data retrieving the specific columns is put to the active sheet.
function myFunction() {
// 1. Set the required columns as the column number.
const requiredColumns = [1, 5, 20]; // Please set the required columns. These values are from your question.
// 2. Retrieve CSV data from an URL.
const url = '###'; // Please set the direct link of CSV data.
const res = UrlFetchApp.fetch(url);
// 3. Parse CSV data.
const ar = Utilities.parseCsv(res.getContentText());
// 4. Retrieve the required columns from the CSV data.
const values = ar.map(r => requiredColumns.map(i => r[i]));
// 5. Put the values to the active sheet.
const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
- If your CSV data uses the specific delimiter, please modify
const ar = Utilities.parseCsv(res.getContentText()); to const ar = Utilities.parseCsv(res.getContentText(), "delimiter");. Ref
Note:
When you want to run the script as the custom function, you can also the following script. In this case, please put =SAMPLE("URL","1,5,20") to a cell. By this, the CSV data retrieving the specific columns is put.
function SAMPLE(url, columns) {
const requiredColumns = columns.split(",");
const res = UrlFetchApp.fetch(url);
return Utilities.parseCsv(res.getContentText()).map(r => requiredColumns.map(i => r[i.trim()]));
}
References:
Added 1:
From your provided sample CSV data, I could understand about the reason of the issue. I think that in this case, the size of CSV data might be large for above method. By this, I think that such error might occur. When I checked the CSV data, it was found that it had 4,763,515 cells with 42,155 rows and 113 columns. So, in order to remove this issue, I would like to propose the 2nd sample script as follows.
In this sample, at first, the CSV data is converted to Spreadsheet using Drive API, and the columns except for the required columns are deleted using Sheets API, and then, the sheet is copied to the active Spreadsheet.
Sample script:
Before you use this script, please enable Drive API and Sheets API at Advanced Google services. I used Drive API and Sheets API because of the large data size.
function myFunction2() {
// 1. Set the required columns as the column number.
const requiredColumns = [1, 5, 20]; // Please set the required columns. These values are from your question.
// 2. Retrieve CSV data from an URL.
const url = "https://www.stanem.it/csv/InnovaCSV.csv"; // This is from your sample CSV data.
const res = UrlFetchApp.fetch(url);
// 3. Convert CSV data to Spreadsheet.
const id = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, title: "tempSpreadsheet"}, res.getBlob()).id;
// 4. Delete the columns except for the required columns.
const ss = SpreadsheetApp.openById(id);
const sheet = ss.getSheets()[0];
const maxColumn = sheet.getMaxColumns();
const requests = [];
for (let i = 1; i <= maxColumn; i++) {
if (!requiredColumns.includes(i)) {
requests.push({deleteDimension: {range: {sheetId: sheet.getSheetId(), dimension: "COLUMNS", startIndex: i - 1, endIndex: i}}});
}
}
Sheets.Spreadsheets.batchUpdate({requests: requests.reverse()}, id);
// 5. Copy the sheet including CSV data to the active Spreadsheet.
const dstss = SpreadsheetApp.getActiveSpreadsheet();
sheet.copyTo(dstss).setName("sheetIncludingCSV");
// 6. Remove the temporat Spreadsheet.
DriveApp.getFileById(id).setTrashed(true);
}
Added 2:
sorry this sheet.copyTo(dstss); works but it creates me a lot of copy sheet, i need only one sheet with always the same name
From above your replying, I modified above script for this.
Sample script:
function myFunction3() {
// 1. Set the required columns as the column number.
const requiredColumns = [1, 5, 20]; // Please set the required columns. These values are from your question.
// 2. Retrieve CSV data from an URL.
const url = "https://www.stanem.it/csv/InnovaCSV.csv";
const res = UrlFetchApp.fetch(url);
// 3. Convert CSV data to Spreadsheet.
const id = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, title: "tempSpreadsheet"}, res.getBlob()).id;
// 4. Delete the columns except for the required columns.
const ss = SpreadsheetApp.openById(id);
const sheet = ss.getSheets()[0];
const maxColumn = sheet.getMaxColumns();
const requests = [];
for (let i = 1; i <= maxColumn; i++) {
if (!requiredColumns.includes(i)) {
requests.push({deleteDimension: {range: {sheetId: sheet.getSheetId(), dimension: "COLUMNS", startIndex: i - 1, endIndex: i}}});
}
}
Sheets.Spreadsheets.batchUpdate({requests: requests.reverse()}, id);
// 5. Copy the values of modified CSV data to a sheet in the active Spreadsheet.
const destinationSheetName = "Sheet1"; // Please set the destilnation sheet name in the active Spreadsheet.
const dstss = SpreadsheetApp.getActiveSpreadsheet();
const values = Sheets.Spreadsheets.Values.get(id, sheet.getSheetName()).values;
Sheets.Spreadsheets.Values.update({values: values}, dstss.getId(), destinationSheetName, {valueInputOption: "USER_ENTERED"});
// 6. Remove the temporat Spreadsheet.
DriveApp.getFileById(id).setTrashed(true);
}
- This sample script puts the modified CSV data to the specific sheet of the active Spreadsheet.
- In this case, the values are put from the 1st row and 1st column. So when you want to put the other range, please modify the script.
