Importing CSV into Google Sheets via Google Script


flickr photo shared by OSU Special Collections & Archives : Commons with no copyright restriction (Flickr Commons)

As part of a future project, I’m looking for easy, automated ways to push/pull CSV files around.

Initially I thought I’d just do the =importdata(‘http://theurl.com/data.csv”) function but I realized that had some drawbacks that made it less ideal which lead to the script below.

Using Google Script triggers this script could be set to retrieve a CSV file every X amount of time and write it to a Google Sheet. The upper portion is adapted from this answer.

In any case, it opens up some decent automatic options and would keep data fresh for easy access charts and graphs in Google.

function importData() {
    var ss = SpreadsheetApp.getActive();
    var url = 'https://myictcorner.wikispaces.com/file/view/TREES.csv/431605548/TREES.csv';
    var file = UrlFetchApp.fetch(url); // get feed


    var csv = file.getBlob().getDataAsString();
    var csvData = CSVToArray(csv); // see below for CSVToArray function
    var sheet = ss.getActiveSheet();
    for (var i = 0, lenCsv = csvData.length; i < lenCsv; i++) {
        sheet.getRange(i + 1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    }

};


// http://www.bennadel.com/blog/1504-Ask-Ben-Parsing-CSV-Strings-With-Javascript-Exec-Regular-Expression-Command.htm
// This will parse a delimited string into an array of
// arrays. The default delimiter is the comma, but this
// can be overriden in the second argument.

function CSVToArray(strData, strDelimiter) {
    // Check to see if the delimiter is defined. If not,
    // then default to COMMA.
    strDelimiter = (strDelimiter || ",");

    // Create a regular expression to parse the CSV values.
    var objPattern = new RegExp(
        (
            // Delimiters.
            "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

            // Quoted fields.
            "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

            // Standard fields.
            "([^\"\\" + strDelimiter + "\\r\\n]*))"
        ),
        "gi"
    );

    // Create an array to hold our data. Give the array
    // a default empty first row.
    var arrData = [
        []
    ];

    // Create an array to hold our individual pattern
    // matching groups.
    var arrMatches = null;

    // Keep looping over the regular expression matches
    // until we can no longer find a match.
    while (arrMatches = objPattern.exec(strData)) {

        // Get the delimiter that was found.
        var strMatchedDelimiter = arrMatches[1];

        // Check to see if the given delimiter has a length
        // (is not the start of string) and if it matches
        // field delimiter. If id does not, then we know
        // that this delimiter is a row delimiter.
        if (
            strMatchedDelimiter.length &&
            (strMatchedDelimiter != strDelimiter)
        ) {

            // Since we have reached a new row of data,
            // add an empty row to our data array.
            arrData.push([]);

        }

        // Now that we have our delimiter out of the way,
        // let's check to see which kind of value we
        // captured (quoted or unquoted).
        if (arrMatches[2]) {

            // We found a quoted value. When we capture
            // this value, unescape any double quotes.
            var strMatchedValue = arrMatches[2].replace(
                new RegExp("\"\"", "g"),
                "\""
            );

        } else {

            // We found a non-quoted value.
            var strMatchedValue = arrMatches[3];

        }

        // Now that we have our value string, let's add
        // it to the data array.
        arrData[arrData.length - 1].push(strMatchedValue);
    }

    // Return the parsed data.
    return (arrData);
};

Comments on this post

  1. Moises Garcia said on October 16, 2017 at 3:02 pm

    Wow, thanks a lot for this script, Tom! I was looking for a way to parse a csv with a delimiter based on a reg exp that would exclude commas in double quotes’ fields, and just found out this!

  2. Michael said on January 20, 2018 at 11:58 am

    working like a charm, thanks!

  3. Regex SEO said on March 8, 2018 at 3:25 pm

    I’m getting error saying “You do not have permission to call setValues (line 11). Can’t find any decent answer how to go around that. Please help.

  4. Richard said on April 17, 2018 at 1:53 am

    Thanks. How do I actually flow the imported csv data into my Google Sheet, e.g. starting from GSheet cell C4?

    • Tom Woodward said on April 17, 2018 at 8:10 pm

      I’m not sure I get what you mean. If you run the function in the Google script editor, the data should get imported.

  5. Richard said on April 18, 2018 at 12:20 am

    As far as I can tell running it via the script editor imports the data into whatever the active sheet (tab) is starting from cell A1. If I use a trigger this always seems to be Sheet1!A1. Suppose I have 3 sheets in my workbook and want the data imported into Sheet3; suppose I have existing content in Sheet3!A1:C3 that I don’t want to be overwritten – how can I specify that the imported csv array should be pasted into Sheet3 starting from cell C4 rather than into Sheet1 starting from cell A1? Thanks.

  6. Tom Woodward said on April 18, 2018 at 10:10 am

    I think sheet.getRange(i + 1, 1, 1, would become sheet.getRange(i + 4, 3, 1 to change the range start row. I think I did the numbers right but didn’t test it. In any case, playing with those first two numbers should move you down and across the sheet.

    • Tom Woodward said on April 18, 2018 at 6:12 pm

      oh yeah – and to change the sheet change var sheet = ss.getActiveSheet(); to var sheet = ss.getSheetByName(‘Sheet3’);

Leave a Reply

Trackbacks and Pingbacks on this post

No trackbacks.

TrackBack URL