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);
};

15 thoughts on “Importing CSV into Google Sheets via Google Script

  1. 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. 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.

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

  4. 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.

  5. 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.

  6. Hello!
    Awesome work!
    Would there be a way of having the script import data only from the last 365 days?

    Thank you!!!

  7. Thanks a lot for sharing this awesome solution – it just works – great job!

    But for us (Swiss guys) there’s a problem with the coding: Our source is in UTF-8 and we do need it in UTF-8 as well, in order to get a fully automated solution.

    Is there any chance, to set the chartset in the script?

    Thank you.

      1. Hi Tom

        Thanks for your reply!

        I tried with adding

        var csv = file.getBlob().getDataAsString(‘UTF-8’);

        but it didn’t work. After checking the data again, it turns out, the data provider accidentally changed the format of the delivered csv file. He switched back to UTF-8 – and everything works, like it should.

        Thanks again!

        Peter

Comments are closed.