6

I'm using ImportHTML() on a Google Sheet to import a large table from a website. It's great, and it's now all there, but now I want to hand-modify the data to my needs. Unfortunately, every time I try to make a change all the data goes away and I get a #REF error in the cell which contained the ImportHTML() equation.

So, I now have a document with a single ImportHTML() cell that magically displays all the data from the website. I want to have an array of cells containing fixed data fetched using ImportHTML(). How can I do this?

|improve this question
  • Do you want to filter the data or perform a query or just keep track of a few values? – Jacob Jan Tuinstra Mar 16 '15 at 18:44
  • Nothing that complex; I'm just hand-editing a table of GPS products to my needs (e.g. removing formatting, adding prices, making certain items clearer to me, etc). – Daniel Griscom Mar 16 '15 at 22:28
5

The solution was to select all the cells (I can't find a Select All menu item, but on OS X Command-A worked), then choose Copy, then Paste special/Paste values only. Bingo: all the cells whose values depend on the ImportHTML() in A1 now have their values pasted.

|improve this answer
  • 1
    That's just like it works in MS Excel! I didn't know that was possible in Google Docs. Thanks! – Ellie Kesselman Mar 15 '15 at 23:44
2

You cannot modify the data provided fetched with IMPORTHTML: if you do, you'll indeed either erase it all (if you modify the cell containing the IMPORTHTML function), or a reference error.

However, you can achieve your goal by referencing the imported data from another sheet. Let's assume you have your IMPORTHTML call within sheet Sheet1. You can create a second sheet, e.g. Sheet2, and insert the following statement in its A1 cell: =Sheet1!A1. Then, by dragging diagonally the blue square that appears at the bottom of cell A1 towards the bottom right of your sheet, you'll get a copy of your imported cells. You can modify any cell you want and leave those you want to keep untouched. For a clean look, you probably want also to hide Sheet1 and only work with Sheet2 from that point on.

|improve this answer
  • Your solution didn't work for me. First, your equation for A1 in Sheet2 was wrong (I kept getting "Error: an array value could not be found"); it needed to be =Sheet1!A1. Second, the result was a Sheet2 where each cell was a reference to Sheet 1, which meant I had to completely re-type any cell I edited, rather than just modifying it. – Daniel Griscom Mar 14 '15 at 19:57
  • Clarification: the second problem was due to my preference, and probably wouldn't be an issue for someone else. The first problem, though, was real. – Daniel Griscom Mar 14 '15 at 20:28
  • You're correct, an array formula won't work but a simple =Sheet1!A1 will work as advertised. The solution with copy paste value that your propose has the advantage that you can edit instead of replace values, however, if the source data changes for some of your unedited values, your spreadsheet will not remain up to date with them. – Jean-Rene Bouvier Mar 16 '15 at 12:08
  • Thanks; you should update your answer to include the correct formula; it will help someone else with different needs from me. – Daniel Griscom Mar 16 '15 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.