Library Subject Guides
In this section we investigate methods for importing data into your spreadsheet from other sources. We take a look at Google Forms, consider approaches to working with live data, and finally dip a toe into the heady world of macros and scripts.
Google Sheets has a number of functions for importing data:
| IMPORTRANGE | Imports data from another Google Sheet |
|---|---|
| IMPORTDATA | Imports data from an online CSV or TSV file |
| IMPORTFEED | Imports data from a newsfeed such as RSS or Atom |
| IMPORTHTML | Imports a table or list from a webpage |
| IMPORTXML | Imports structured XML data — basically does what all the others above do, but in a more technical way |
In most cases when you're importing data, a simple copy and paste will be sufficient. Where these import functions come in useful is when you need to work with live data: data that is constantly changing or being updated.
The IMPORTRANGE function imports ranges of data from other Google Sheets workbooks. Like other data functions we've seen, the output is a live representation of the original data: if the original data changes, so will the output.
=IMPORTRANGE("URL","range")
The formula needs the web address (URL) of the sheet you're importing from, and the range you wish to import (including the sheet reference, unless you're importing a named range). Both arguments need to be enclosed in quotes (although they can also be referenced from values elsewhere in your spreadsheet).
Here's an example:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/
1P4lIwzp2iou2ozKYOFwxdlM1CwXSi39ArNaqjoH4J-4/edit#gid=0","Sheet1!B:B")
You can just use the 'key' part of the URL if you prefer. Here's a tidier example that also uses a named range:
=IMPORTRANGE("1P4lIwzp2iou2ozKYOFwxdlM1CwXSi39ArNaqjoH4J-4","enrolled")
If access to the referenced sheet is in any way restricted, you'll get a #REF! error when you first enter the function. Hover over the cell to bring up a button to allow access to the sheet:
You need to have access to the referenced spreadsheet in order to allow access with this button. But other collaborators on the destination spreadsheet don't need to have access to the referenced spreadsheet.
Be aware that once access to a spreadsheet has been allowed, an editor could modify the range being pulled across. If this is a concern, you could always 'bounce' the import through a hidden and protected sheet or even through an intermediary spreadsheet!
The other import functions work in much the same way as IMPORTRANGE, but with different types of web content. For example:
=IMPORTFEED("http://feeds2.feedburner.com/uni-of-york-library-news")
...will import the University of York Library news feed,
=IMPORTHTML("https://www.york.ac.uk/about/financial-information/","table",2)
...will import the second table from the University's financial information page,
=IMPORTDATA("http://data.cyc.opendata.arcgis.com/
datasets/8c16fc70e4e0452ea750361ea0e19d4d_8.csv")
...will import a table of York council wards from York Open Data, and...
=IMPORTXML("https://status.york.ac.uk/","//li")
...will bring all the list items from the IT Services status monitor so you can see what systems are down without having to leave the comfort of Google Sheets!
Because CSVs and TSVs are basically just text files, IMPORTDATA can be used to import pretty much any text file. You could also import data into a spreadsheet from a web service such as an API (application programming interface), though it may be better to do this using Apps Script... If you're wanting to import text from a webpage, IMPORTXML may be the better option (you could use "//p" to import every paragraph on the page).
Data from another Excel workbook can be imported within a function as a cell or range reference using the following syntax:
'file-path[file-name]sheet-name'!cell(s)
For example:
='T:\IT Training\Essential Spreadsheets\[T1 - Demo.xlsx]Sheet1'!A3:A8
...would fetch across cells A3:A8 from Sheet1 of the file T1 - Demo.xlsx in the folder T:\IT Training\Essential Spreadsheets\
Excel also has a number of data import options. They can be found at Data > Get & Transform Data
For instance, to import a table of data from a webpage, you might do the following:
As with the Query Editor, you'll need to edit the connection properties if you want the data to refresh automatically.
Like Google Sheets, Excel has a way of fetching web content within a function: WEBSERVICE — this will fetch data for pretty much the same things as Google's range of import functions (though it doesn't work on a Mac). For instance:
=WEBSERVICE("http://feeds2.feedburner.com/uni-of-york-library-news")
...will import the University of York Library news feed in its raw XML form.
There's a further function, FILTERXML, which can parse XML and return specific items. It works pretty-much exactly like Google's IMPORTXML, but is a lot more fussy (it will only work with explicit XML, so won't parse HTML, for instance). Here's an example, using the same Library news feed:
=FILTERXML(WEBSERVICE("http://feeds2.feedburner.com/uni-of-york-library-news"),"//title")
...which will pull through just the headline text.
As its name suggests, WEBSERVICE can be used to import data from a dedicated web service such as an API (application programming interface).
Both Google Sheets and Excel for Microsoft 365 have functions which pass a query to a Google/Microsoft web-service and return the result:
| DETECTLANGUAGE | | Identifies the language of a text string |
|---|---|---|
| GOOGLEFINANCE | | Imports data from Google Finance |
| GOOGLETRANSLATE | | Translates a text using Google Translate |
| STOCKHISTORY | | Imports historical financial data |
Excel for Microsoft 365 has some very useful web-service features such as Data > Data Types where you can get financial and geographical information from a cell's contents. It also has Review > Language > Translate, but that operates from a side-bar reference page, rather than in a way that generates new data.
Data from a Google Form can be fed in real time to a Google Sheet. This is especially handy if responses are coming in continuously and back-end processes need to be performed.
Once a Google Sheet has been connected to a Google Form, responses to the form will also appear in the spreadsheet.
Deleting items in the spreadsheet does not delete items from the Form's responses tab. If you need to clear out data, be sure to do it in both places.
Working with live data – data that's constantly being updated from an outside source – can be tricky. It might keep changing shape or growing. This presents challenges if you're wanting to interrogate the data you're importing.
As a general rule, it's best to do any calculations on a separate sheet to your data, just in case that data changes shape and starts to clash with whatever else you're doing.
If you need to pull across parts of your data onto another sheet, you could use the Query options we looked at in the Processing section. Or you could make use of some of the other data functions. We've already looked at data functions such as SORT and FILTER, but there are some other approaches to consider.
ARRAYFORMULA The ARRAYFORMULA function in Google Sheets lets you apply a single formula across a whole range. At its simplest...
=ARRAYFORMULA(A:A)
...will just bring through everything in column A, while...
=ARRAYFORMULA(IF(ISBLANK(A:A),,A:A+1))
...will do nothing if a value in column A is blank, but will add
As a general rule, you can wrap a function in ARRAYFORMULA and replace any cell references with ranges. Some functions which rely on ranges won't be able to work in this way.
Remember, too, that Google sheets can do open-ended ranges (e.g. A2:A which starts part-way down a column but continues to the end) — these can be very useful in ARRAYFORMULAe.
Dynamic arrays in Excel Excel has long had support for array formulae, but they're kind of weird, involve pressing Ctrl, Shift, and Enter together, have funny curly brackets, and aren't very easy to work with.
However in Excel for Microsoft 365, there are now dynamic arrays, and they're very Googly indeed. They basically work like ARRAYFORMULA but without having to write 'ARRAYFORMULA' — so...
=A:A
...will bring through everything in column A, while...
=IF(ISBLANK(A:A),"",A:A+1)
...will do nothing if a value in column A is blank, but will add 1 if it's not.
Be careful with full-column ranges in Excel as they're effectively infinite. Excel will complain if you try to reference a full column when you're not in row 1 so you may need to set arbitrary limits to your ranges (e.g. A2:A1000).
If a dynamic array happens and you don't want it to, stick an @ in front of the reference and it should behave itself.
You can do a lot more with spreadsheets than what you see on the menus (and there's a lot of stuff there that we haven't looked at on these pages). Both Excel and Google Sheets come with their own scripting environments, using Visual Basic for Applications (VBA) in Excel and Google Apps Script in Google Sheets.
IT Services don't offer support in the use of VBA at York, though you'll find a lot of help online should you wish to play by yourself. We do offer support for Apps Script, which is basically a version of JavaScript, and we even have a self-paced online Apps Script course (similar to this guide) you can work through.
For simpler tasks you don't really need to do any coding for these things. Instead, you can just 'record' a macro (effectively a sequence of actions).
Macros are particularly useful for carrying out repetitive tasks involving a handful of steps. You effectively record yourself doing those steps, and you can then replay those actions again and again.
Before you record, you'll need to decide whether or not you need to use absolute or relative references:
Recording a macro in Excel Before you can record a macro in Excel, you will need to enable the Developer tab (which is hidden by default to stop you breaking things):
To record a macro:
Macros cannot be saved in a .xlsx file. Instead you'll need to save your document as a macro-enabled workbook: .xlsm. Because macros can do stuff automatically, expect the odd warning or two when opening a .xlsm file. You should always exercise a degree of caution when faced with a strange .xlsm file, so expect your work colleagues to do the same! People might need reassurance, or might choose not to enable the macros you've created when working in the document themselves.
Recording a macro in Google Sheets 
The first time you use your macro you will need to authorise it: this will basically give Google Sheets permission to do things to your spreadsheet using the macro.
Apps Script Google Apps Script is a JavaScript-based programming language which can be used with the various Google applications, including Google Sheets. Any macros that you record in Google Sheets are recorded in Apps Script, and if you know what you're doing you can build more elaborate processes in the scripting environment at Extensions > Apps Script.
As well as doing things within the spreadsheet, Apps Script can be used to connect your spreadsheet to other Google Apps such as Mail, Calendar or Docs. It can also be used to communicate with third-party tools or to generate webpage content.
