Google Sheets - Referencing Data in an excel spreadsheet

[jharvey87]

Good morning,

My company uses the Google Apps program and therefore I'm trying to find a better way to transfer data over into Google Sheets.

Right now, I use several SQL Stored Procedues that spit out data from our system into an excel spreadsheet. Right now my current process is to refresh the data and then paste it into a Google Sheet every day. We have several company Google Sheets that reference this data using the Google "IMPORTRANGE" function. Does anyone have any suggestions on how this might be improved? I don't think we can have our queries be put into Google Sheets due to security reasons. Is there anyway to import data from an excel sheet that is saved on Google Drive? Or perhaps a macro that could somehow paste Excel data into an existing Google Sheet? Really trying to eliminate the manual upload part as it takes long as its thousands of rows of data.

Any suggestions would be greatly appreciated.

[6StringJazzer]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however "Google Sheets" does not really convey what your request is about--it applies to almost every question in this subforum. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[jharvey87]

Respectuflly I've been using this forum for several years, I love it, ton of great help here. However, I honestly don't know how I could explain what I'm looking for any better then what I posted. I was simply asking is there a way to pull data from an "importrange" type function that is from excel spreadsheet rather than a Google Sheet. For example, you save the Excel on your Google Drive, can a google sheet reference data on the XLS file, or is that not possible. In short, I'm trying to avoid the copying and pasting of data from Excel into Google Sheets as noted above.

[AliGW]

Your title could be: "Find more efficient way to transfer data from Excel to Google Sheets than copy/paste."

Respectfully, "Google Sheets" tells us nothing of any value whatsoever.

[6StringJazzer]

I honestly don't know how I could explain what I'm looking for any better then what I posted.

Your post is OK. It's the title that's the problem.

[AliGW]

I suggested a suitable title in post #4. Please use that.

[janmorris]

@jharvey87

You cannot use the IMPORTRANGE() function to directly import data from an Excel spreadsheet. The IMPORTRANGE() function will only import data from a Google spreadsheet.

Option 1:
Upload the Excel spreadsheet into Google Drive and convert it to a Google spreadsheet. You can then use the IMPORTRANGE() function to import the data from the converted file.

Option 2:
Export the Excel spreadsheet to a CSV file, then use the IMPORTDATA() function to get the data into a Google spreadsheet.

In either case, if you have master data that different users or user groups should only have access to partial data, or if you want to ensure that they do not have access to the complete dataset, then you should have a system that is structured like this:

safe-structure.png

this type of structure will allow you to add more source data or renew source data because the intermediate file will prevent direct access to the source data file.