I've been stuck on this issue for a while and I've experimented with different methods, but I'm too inexperienced to know how to utilize the different tools available to make this work.
I have anywhere from 6 to 38 columns in each CSV and I only need about 4 columns from each of them. The columns will be different for each one, so I need to customize the removal process.
What are some different methods I could use for removing unwanted columns from a CSV (started as a URL, then a blob, then a string, finally an array) before importing only the desired columns into Google Sheets?
function urlsToSheets(){
importData("https://hub.arcgis.com/datasets/d3cd48afaacd4913b923fd98c6591276_36.csv", "Pavement Condition");
importData("https://hub.arcgis.com/datasets/lahub::tctmc-streets-of-significance-construction-impacted-streets.csv", "Streets of Significance");
importData("https://geohub.lacity.org/datasets/lahub::one-year-moratorium-streets.csv", "One-Year Moratorium");
importData("https://hub.arcgis.com/datasets/lahub::boe-permits-lines.csv", "BOE Permit Lines")
importData("https://hub.arcgis.com/datasets/lahub::archived-boe-permits-lines.csv", "Archived BOE Permit Lines");
importData("https://hub.arcgis.com/datasets/lahub::boe-permits-points.csv", "BOE Permit Points");
importData("https://hub.arcgis.com/datasets/56318ef6ed6444d981977adf80157b87_5.csv","Archived BOE Permit Points");
}
function importData(url, sheetName){
/** Moving Data from URLs into Sheets */
const file = UrlFetchApp.fetch(url);
const csv = file.getBlob().getDataAsString();
const csvData = csvToArray(csv);
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
sheet.clear();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
function csvToArray(strData, strDelimiter){
/** Cleaning Data in URLs and Converting to 2D Array*/
strDelimiter = (strDelimiter || ",");
var objPattern = new RegExp(
(
// Delimiters.
"(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
// Quoted fields.
"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
// Standard fields.
"([^\"\\" + strDelimiter + "\\r\\n]*))"
),
"gi"
);
var arrData = [
[]
];
var arrMatches = null;
while (arrMatches = objPattern.exec(strData)) {
var strMatchedDelimiter = arrMatches[1];
if (
strMatchedDelimiter.length &&
(strMatchedDelimiter != strDelimiter)
) {
arrData.push([]);
}
if (arrMatches[2]) {
var strMatchedValue = arrMatches[2].replace(
new RegExp("\"\"", "g"),
"\""
);
} else {
var strMatchedValue = arrMatches[3];
}
arrData[arrData.length - 1].push(strMatchedValue);
}
return (arrData);
}