Google Blogoscoped


Extracting info from certain colums in Spreadsheets?

Philipp Lenssen [PersonRank 10]

Wednesday, June 30, 2010
10 years ago7,939 views

I have a multi-sheet Google Spreadsheets document. Some columns in these sheets contain important information which I would like to extract into an overview list on a first sheet. Is there some formula or so for this, which would automatically update as new info comes along? The columns are fixed, so I know which ones are important...

Basically, my sheets contain projects information, and I have some orange-colored colums containing my todos. I'd be interested to pull these todos into an overview sheet for a sort of "next todos" list. Importantly, a column may be mostly empty, except for one or two rows with todos in that column...

Tony Ruscoe [PersonRank 10]

10 years ago #

I'm not sure whether you can do this using simple formulas (assuming you want to exclude any blank rows?) but you could definitely do it using Google Apps Script. I've been using this for a few things recently and it's really quite easy to get the hang of if you're familiar with JavaScript. Here are the docs: ...

If I correctly understand what you've got at the moment, you'd want to create a function that loops through your worksheets, then loops through the rows of your "todo" columns and inserts any non-blank values into the first worksheet, putting each value on a new row. You could trigger this to happen whenever you open the spreadsheet (clearing the existing values before populating new ones) or each time you make an edit to any of those columns / rows.

Or you could just set it up to automatically insert the "todo" in the first sheet whenever you enter it rather than doing all the looping. But they you'd also need to remove it somehow, so looping might be the best solution here.

Philipp Lenssen [PersonRank 10]

10 years ago #

Thanks! Yes, your solution seems to be a fit, your description of what should happen is exactly right. I will try this.

This thread is locked as it's old... but you can create a new thread in the forum. 

Forum home


Blog  |  Forum     more >> Archive | Feed | Google's blogs | About


This site unofficially covers Google™ and more with some rights reserved. Join our forum!