2

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);
}
Apalila
  • 53
  • 6
  • Personally I would prefer converting them to 2D arrays with Utilities.parseCsv() an then remove the columns or simply remove the columns before you create the csv – Cooper Jun 08 '22 at 16:49
  • When I first started this script I tried Utilities.parseCsv() but two of my URLs came back with errors when trying to parse (some issue with comma placement or \n), so I designed this method. – Apalila Jun 08 '22 at 16:53
  • Deleting columns with array methods is very easy – Cooper Jun 08 '22 at 16:55
  • 1
    https://stackoverflow.com/questions/65890867/google-script-import-only-few-column-with-script – Cooper Jun 08 '22 at 16:56
  • Thank you Cooper!! That 100% solved my problem AND I understand what going on in the script. Thank you!! – Apalila Jun 08 '22 at 17:12

0 Answers0