Skip to Main Content
University of York Library
Library Subject Guides

Essential Spreadsheets: a Practical Guide

X2: Connecting data

Spreadsheets were developed as a tool to store, analyse and manipulate numerical data. They are now commonly used for working with sets of data containing both text and numbers, and for generating graphs and charts. They're also brilliant. Let's play with

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.

Import functions in Google Sheets

Google Sheets

Google Sheets has a number of functions for importing data:

IMPORTRANGEImports data from another Google Sheet
IMPORTDATAImports data from an online CSV or TSV file
IMPORTFEEDImports data from a newsfeed such as RSS or Atom
IMPORTHTMLImports a table or list from a webpage
IMPORTXMLImports 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.

IMPORTRANGE

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:


Tip

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

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/student-statistics/admissionsstatistics/","table",2)

...will import the second table from the University's admission statistics 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!

Tip

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

Importing data in Excel

Excel

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\

Importing from other sources

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:

  1. Go to Data > Get & Transform Data > From Web
  2. In the URL field of the "From Web" dialogue, paste in the web address of the page you're importing from
  3. Hit OK to open the "Navigator" dialogue:
  4. Importing data from the Wikipedia page on the 1982 Eurovision Song Contest, natch
  5. The left-hand pane shows a list of all the tables on the webpage; choose the one you need. A preview will be shown in the right-hand panel
  6. You can choose to Transform Data using the Query Editor, or Load as an Excel table.
Tip

As with the Query Editor, you'll need to edit the connection properties if you want the data to refresh automatically.

WEBSERVICE and FILTERXML

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.

Tip

As its name suggests, WEBSERVICE can be used to import data from a dedicated web service such as an API (application programming interface).

Built-in web service functions

Both Google Sheets and Excel for Microsoft 365 have functions which pass a query to a Google/Microsoft web-service and return the result:

DETECTLANGUAGEGoogle SheetsIdentifies the language of a text string
GOOGLEFINANCEGoogle SheetsImports data from Google Finance
GOOGLETRANSLATEGoogle SheetsTranslates a text using Google Translate
STOCKHISTORYExcelImports 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.

Google Forms

Google Sheets

Google FormsData 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.

The 'Create spreadsheet' button on the Responses tab in Google Forms lets you select or create a spreadsheet to receive your form responses

Once a Google Sheet has been connected to a Google Form, responses to the form will also appear in the spreadsheet.

Tip

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

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.

Google SheetsARRAYFORMULA

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 1 if it's not.

Tip

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.

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

Tip

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.

Macros and scripts

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:

  • With absolute references, any actions you perform in a particular cell will always be performed in that cell;
  • With relative references, any actions you perform in a particular cell will be considered relative to the cell you were in when you started recording.

ExcelRecording 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):

  1. On the File tab, go to Options > Customize Ribbon
  2. Ensure Main Tabs is selected in the "Customize the Ribbon" dropdown, and select the Developer check box.
  3. Hit OK and the Developer tab should mow be visible.

To record a macro:

  1. If you're using relative references, select Developer > Code > Use Relative References, and ensure your selected cell is the cell you want to refer from;
  2. Choose Developer > Code > Record Macro — the "Record Macro" dialogue will open:

  3. Give your macro a decent name and describe what it does. You can also assign a shortcut key for playing your macro;
  4. Hit OK — your macro is now recording;
  5. Carry out the various tasks you want your macro to perform;
  6. When you're done, select Developer > Code > Stop Recording (which is where the 'Record Macro' option used to be);
  7. To run your macro, you can open the "Macro" dialogue from Developer > Code > Macros, select the macro you want to run, and hit Run (or use the shortcut you assigned, if you assigned one).

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.

Google SheetsRecording a macro in Google Sheets

  1. If you're recording relatively, ensure you're in the cell you need to work from;
  2. Go to Extensions > Macros > Record macro — the recording dialogue opens:
  3. Options for recording relatively or absolutely are on the dialogue, which stays on screen throughout recording
  4. Choose whether you're working with absolute or relative references;
  5. Perform the tasks you wish to record;
  6. Hit Save to save your macro — the "Save new macro" dialogue opens;
  7. Give your macro a good name and an optional shortcut then hit Save again;
  8. To run your macro, go to Extensions > Macros and select it from the list (or use your shortcut if you assigned one).

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.

Google SheetsApps 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.

Feedback
X