Export Excel to Google Sheets – Utilize the Cloud and access your Excel Data anywhere!

Linking Excel to a cloud platform has been a thorn in my side for a long time – however once complete it works perfectly and you can access any data stored in Excel from Google Sheets. It doesn’t even matter what version of Excel you have!

Thankfully (sort of…) Microsoft are doing work in SharePoint and OneDrive, which could make this method obsolete in the future. However for now, this is my preferred method.

 

Steps

1. Install Google Backup and Sync

2. Generate CSV File

3. Import CSV File to Google Sheets

4. Done

 

 

Watch a 7 minute tutorial video, or read on and download below!

Install Google Backup and Sync

Assuming you are familiar with Google Sheets (if not go have a Google and have a play), the plan is to back up a CSV file to Google Drive, and then use a script to convert it into an array.

1. Download and install Google’s Backup and Sync. This is the same as OneDrive – note Windows XP does not work (which is very frustrating).

2. Following the prompts, get it to backup a folder, for this example call it “Excel Data”. Every other setting is up to you.

1.png

Generate CSV File

We now need to move our data to the “Excel Data” folder and saved as a CSV.

1. Create a macro enabled workbook (this can be anywhere you like), for this example call it “Data Copier”.

2. Create a normal Excel file (this can be anywhere you like), for this example call it “Data”. Add in a table with some data (this doesn’t need to be an actual table), change the sheet name to “DataTable1” and save/close.

2.png

3. Open “Data Copier” and rename Sheet1 to “Settings”.

4. In A1, write “Data to Copy (Workbook)”, in A2 write “Backup and Sync Folder” and in A3 write “Name of CSV file”.

5. In B1, add in the location of your workbook, mine is “C:\Users\Username\Desktop\Work\Excellen\Export Excel To Google Sheets\Excel Sheets\Data.xlsx”

6. In B2, add in the location to your Backup and Sync folder, mine is “C:\Users\Chris Norris\Desktop\Work\Excellen\Export Excel To Google Sheets\Excel Data”

7. In B3, add in the name you want your CSV file to be called – I just put in “Data”.

3.png

8. Navigate to the VBA editor (alt+F11, or in the developer pane).

9. Insert a module.

4.png

10. Copy in the following Script:

Sub CopyDataToGS()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    'Tells Excel contents are strings
    Dim DataWB As String
    Dim SaveLocation As String
    
    'Looks up data in the Settings sheet'
    DataWB = Sheets("Settings").Range("B1").Value
    SaveLocation = Sheets("Settings").Range("B2").Value & "\" & Sheets("Settings").Range("B3").Value & ".csv"
    
    'Opens Data sheet
    Workbooks.Open DataWB
    
    'Saves CSV file into Backup and Sync Workbook
    ActiveWorkbook.SaveAs Filename:= _
    SaveLocation _
    , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.Close
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

11. Running the macro will now copy the Data sheet into the Backup and Sync folder as a CSV file.

5.png

Import CSV file to Google Sheets

Now all we do is create a Google Sheets script, that processes the CSV back into a data table.

1. Create a Google Sheet workbook (call it whatever you want).

2. Rename Sheet1 to “Data Dump”.

3. Open the Script Editor.

6.png

4. Name the project anything, and overwrite the current code with the following:

function importData() {
    var ss = SpreadsheetApp.getActive();
    var url = 'https://drive.google.com/uc?export=download&id=1xg14JdYLavfeF1kvAmYfmU_EqSAGSVNCUT0o'; //Replace this with your Link
    var file = UrlFetchApp.fetch(url); // get feed
 
 
    var csv = file.getBlob().getDataAsString();
    var csvData = CSVToArray(csv); // see below for CSVToArray function
    var sheet = ss.getSheetByName('Data Dump'); //Replace this with the sheetname you want the data to appear in
    for (var i = 0, lenCsv = csvData.length; i < lenCsv; i++) {
        sheet.getRange(i + 1, 1, 1, csvData[i].length).setValues(new Array(csvData[i])); //https://stackoverflow.com/questions/11947590/sheet-getrange1-1-1-12-what-does-the-numbers-in-bracket-specify
    }

};


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

5. Open a new tab, and find the CSV file on Google Drive. It will be under the “Computers” tab.

6. Right click on it, click share, click on the “Get shareable link” button, change it so anyone can edit, copy and click done.

7. Copy it over to a notepad and copy the Google document code.

8.png

8. Go back to the script, and paste it over the document code (not the entire link!) in line 3.

9. Make sure line 9 has the name of the sheet where you want the data to imported to.

10. Click the Debug button, change the dropdown to “ImportData” and click the Debug button again.

11. Follow the tedious Authorization process.

12. Likely errors would be if your CSV link was incorrect, or your CSV file is too large. Double check your link, and start small before scaling up.

13. Go back to your Google Sheet, and see your data!

10.png

That’s it!

I recommend not formatting or adding formulas to the “Data Dump” sheet. Simply create a new sheet, and then link to, and format the data to your heart’s contents!

Where to from here? Follow my schedule excel macro tutorial to run your Excel CSV macro every day or every hour, then learn how to setup a Google Sheets Script Trigger to schedule the Import Data script to also run daily or hourly.

You then have Excel data on the cloud! Great for dashboards and using Google’s API to link to other apps!

7. Download

Name
Name
Helps me to design future templates that you will find relevant
Let me know if you need help, and I would be thrilled to learn what you are using this template for!

More templates