save the values from spreadsheet to an array I can use in javascript for google charts

I have a number of tables I am creating Google charts for. I need for the values in the spreadsheet to be converted into an array I can use for the javascript (see example). Does anyone know the best way to do this quickly format the attached data into an array like this.

          ['Apples',  new Date (1988,0,1), 1000, 300, 'East'],
          ['Oranges', new Date (1988,0,1), 1150, 200, 'West'],
          ['Bananas', new Date (1988,0,1), 300,  250, 'West'],
          ['Apples',  new Date (1989,6,1), 1200, 400, 'East'],
          ['Oranges', new Date (1989,6,1), 750,  150, 'West'],
          ['Bananas', new Date (1989,6,1), 788,  617, 'West']
Estimates-Total-Counties-TSDC.xls
Oscar9ertangoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
How would the data in the attached file look when it's converted?

Like this?

['HGAC-13','6056688','5990432','5918550','5859339','5796975','5739300','5734497','5625949','5463804'],['HGAC-8','5862603','5796457','5724337','5665822','5604705','5547134','5541800','5434051','5273593'],['Austin','27472','27412','27357','27270','27182','27095','27335','26928','26327'],['Brazoria','312852','308890','304844','301011','296691','293315','291729','286773','280276'],['Chambers','34538','34023','33043','33043','33225','32938','32664','32383','33037'],['Colorado','21753','21758','21813','21774','21725','21691','21925','21629','20935'],['Fort Bend','573700','560064','545556','533696','523339','510868','503315','487047','467792'],['Galveston','287918','287964','288154','288154','286987','285393','285484','282126','277459'],['Harris','4083368','4044032','4003115','3965716','3922115','3891420','3899122','3830130','3717506'],['Liberty','77982','77807','77446','77491','77451','77350','77382','77176','76943'],['Matagorda','37461','37439','37616','37375','36862','36923','36930','37063','37113'],['Montgomery','453571','444895','433425','427960','425999','417095','412665','399941','383975'],['Walker','64752','64750','64739','64555','64239','64204','64245','64026','63602'],['Waller','38674','38782','38754','38751','38898','38755','39439','38475','36605'],['Wharton','42647','42616','42688','42543','42262','42253','42262','42252','42234']
0
Oscar9ertangoAuthor Commented:
That is close but I need to know how to handle that dates also.

But it would help me just to get this far, I could edit it from there. I would just need to remove the quote marks from the numbers and add the date values

How did you get this result, was it something automatic?

Thx
0
Oscar9ertangoAuthor Commented:
Need it more like this:

['Chambers', new Date (1988,0,1), 34538, 34023, 33043, 33043, 33225, 32938, 32664, 32383, 33037],
['Colorado',new Date (1988,0,1), 21753, 21758, 21813, 21774, 21725, 21691, 21925, 21629, 20935],
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NorieAnalyst Assistant Commented:
There are no dates in the actual data, the only dates are in the header.

So where does the date in new Date(1988,0,1) come from?

Also, where do you want to ouput the data?
0
Oscar9ertangoAuthor Commented:
That is one thing I need answered. How do I format the dates in the header to put it in code

The output should be a Google motion chart like the following page (you can see the code if you select "view source"). http://futurehoustonsupport.com/motioncharttest.html

The date comes from this I used it in the example b/c I dont know how to format it but it should be the yearly values 2000 - 2012
0
Julian HansenCommented:
As imnorie said - no clear informatio non where the date comes from - your output does not really make sense - with respect to dates - when compared to the data.

Having said that you can use this formula in the spreadsheet to get most of the way there
="['" & A2 & "', new Date(1988,0,1)',"&B2&","&C2&","&D2&","&E2&","&F2&","&G2&","&H2&","&I2&","&J2&"]"

Open in new window

Paste into K2 and copy down
0
Julian HansenCommented:
That is one thing I need answered. How do I format the dates in the header to put it in code
No, what needs to be answered is why your resulting line only has one date but each column has its own date.
0
Oscar9ertangoAuthor Commented:
Sorry I shouldn't have used that example, I just meant that is what the format is.

I'm using the values in the spreadsheet for an array in a HTML page.
0
Oscar9ertangoAuthor Commented:
@imnorie were you able to get that array in some sort of automatic way, meaning were you able to save or export the sheet to get those values. That gets me a lot of the way there.

I can accept that as an answer and maybe will break the question into some separate ones. about how to deal with the dates.

Thx
0
Julian HansenCommented:
Did you see this post a39467445

Sorry I shouldn't have used that example, I just meant that is what the format is.
Can you manually construct what the data should look like from the data in  the spreadsheet - exactly as you want it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
I can get the result using formulas on a sheet, not as an array though, but what you originally posted isn't really an array.

If you want that result in a text file then using code might be the way to go.

But the date thing still isn't resolved.

Do you want the headers as an element of the 'array'?
0
Julian HansenCommented:
One correction to the formula - add a ',' on the end
="['" & A2 & "', new Date(1988,0,1)',"&B2&","&C2&","&D2&","&E2&","&F2&","&G2&","&H2&","&I2&","&J2&"],"

Open in new window

You will need to manually strip the last comma.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.