Newbie: generalDataSetQuery

[Bas]

Hi All,

I am wondering. Could I lookup two columns for my query ?

Or do I have to make another optional appendquery1 argument 

for that ?

Best regards,

Bas

[Bas]

Op zaterdag 6 december 2014 13:36:05 UTC+1 schreef Bas:

see

http://xliberation.com/docs/docsRestLibrary.html

and

http://ramblings.mcpher.com/Home/excelquirks/json/rest/advanced

Public Function restQuery(Optional sheetName As String = vbNullString, Optional sEntry As String = vbNullString, Optional sQuery As String = vbNullString, Optional sQueryColumn As String = vbNullString, Optional sRestUrl As String = vbNullString, Optional sResponseResults As String = vbNullString, Optional bTreeSearch As Boolean = True, Optional bPopulate As Boolean = True, Optional bClearMissing As Boolean = True, Optional complain As Boolean = True, Optional queryCanBeBlank As Boolean = False, Optional sFix As String = vbNullString, Optional user As String = vbNullString, Optional pass As String = vbNullString, Optional append As Boolean = False, Optional stampQuery As String = vbNullString, Optional appendQuery As String = vbNullString, Optional collectionNeeded As Boolean = True) As cRest

set sQueryColumn to the name of the column that contains the query data, appendQuery to some additional string you want to add to the constructed query.. so the finally constructed string will like this

sRestUrl + query data pickedup from sQueryColumn + appendQuery

also, If you are using a library entry eventually there is an additional opportunity to add another append string  which would be inserted before this appendQuery but after the one from sQueryColumn. This is often a better place to put it if it is standard.

alternatively, you could just change the url to include the addition stuff before your column query .. for example

sQueryUrl = "http://somewhere?key" & someKey() & "&someparameter="

an example is

        With .add("fusiondata")

            .add "restType", erSingleQuery

            .add "url", "https://www.googleapis.com/fusiontables/v1/query?key="

            .add "results", ""

            .add "treeSearch", True

            .add "ignore", vbNullString

            .add "append", "&sql="

        End With

also, If you are using a library entry eventually there is an additional opportunity to add another append string  which would be inserted before this appendQuery but after the one from sQueryColumn. This is often a better place to put it if it is standard.

sQueryColumn As String = vbNullString, Optional sRestUrl As String = vbNullString, Optional sResponseResults As String = vbNullString, Optional bTreeSearch As Boolean = True, Optional bPopulate As Boolean = True, Optional bClearMissing As Boolean = True, Optional complain As Boolean = True, Optional queryCanBeBlank As Boolean = False, Optional sFix As String = vbNullString, Optional user As String = vbNullString, Optional pass As String = vbNullString, Optional append As Boolean = False, Optional stampQuery As String = vbNullString, Optional appendQuery As String = vbNullString

>So this means I could not have data in columnA and columnB to make a query and include my apikey ?

Kind regards,

Bas

 

[Bruce Mcpherson]

There are a few places to add variable data, but only one query column … so its not possible to pick up multiple columns to generate a query. Is there any reason you couldn't simply generate an extra column with the concatenation?

if necessary, you could do this easy programatically before doing your query .. I'm just typing this in so there might be some errors..

dim ds as new cdataset,dr as cdatarow

' add a new column if necessary

with ds.load("some sheet")

  .where.resize(1).offset(-1,.columns.count).value ="new key"

  .teardown()

end  with

' create new column data

set ds = new cdataset

with ds.load("some sheet")

  for each dr in .rows

    dr.column("new key").value = dr. column("key1").tostring & dr. column("key2").tostring

  next dr  

  .bigcommit()

  .teardown()

end with

now you can do your query using "new key"

--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Bas]

Woow thats fast. To clarify my search. I want google to tell me the distance between pairs of two postalcode area's.

In the browser I type https://maps.googleapis.com/maps/api/directions/json?origin=3012JR&destination=1012JS&key=AIza...googleauthorizationkey

So I have  1. https://maps.googleapis.com/maps/api/directions/json?origin= 2. ColumnAvalue 3012JS 3."&destination" 4. ColumnBvalue 1012JS

Kind regards,

Bas


Op zaterdag 6 december 2014 16:59:16 UTC+1 schreef bruce:

[Bruce Mcpherson]

ok .. so you'd have something like

dr.column("new key").value = "&origin=" & dr. column("key1").tostring & "&destination=" & dr. column("key2").tostring

you can put your api key first then you can make it part of the url, so you wouldnt need an append

something like 

https://maps.googleapis.com/maps/api/directions/json?key=apikey

bruce

[Bas]

Hi Bruce,

I while creating the columndata I 

ran into some errors which I am not able

to resolve :-(

.bigcommit() Compile error:

Expected =

and .teardown gives me Compile error: 

Syntax error

 

 

Op zaterdag 6 december 2014 18:36:58 UTC+1 schreef bruce:

[Bruce Mcpherson]

Omit the brackets

Sent from my iPad

[Bas]

Executing this

Public Sub testPostcodeafstanden()

   Dim ds As New cDataSet, dr As cDataRow

Set ds = New cDataSet

With ds.load("postcodeafstand")

  For Each dr In ds.rows

    dr.columns(3).value = "&origin=" & dr.columns(1).toString & "&destination=" & dr.columns(2).toString

    Next dr

 .bigCommit

 .tearDown

End With

generalDataSetQuery("postcodeafstand", "postcodeafstanden", "dr.columns(3).value", , True).tearDown

End Sub

gives me the error:  The following required columns are missing from dataset postcodeafstan: dr.columns(3).value

While I see the contents of column 3 in my sheet.

What am I doing wrong ?

Kind regards,

Bas

Op zondag 7 december 2014 15:14:19 UTC+1 schreef bruce:

[Bruce Mcpherson]

firstly, you needed to create a heading for column 3 , as per my original example.. (did you already do this) .. lets say it was called "xxx"

you would then pass "xxx" to restquery.. it's expecting a column name,

...although you can use column numbers, it's better to user their heading names, so if your sheet changes everything will still work

 dr.columns("xxx").value = "&origin=" & dr.columns("aaa").toString & "&destination=" & dr.columns("bbb").toString

finally I'd probably replace .bigcommit with .column("xxx").commit

that way it will only write back one column with the changes (in case you have some formulas or something)

i haven't checked the rest of you code...

[Bas Neve]

origin destination makeurl legs.distance.text
3012 JR 1012 JS &origin=3012 JR&destination=1012 JS

Public Sub testPostcodeafstanden()
Dim ds As New cDataSet, dr As cDataRow

Set ds = New cDataSet
With ds.load("postcodeafstand")
For Each dr In ds.rows
dr.columns(3).value = "&origin=" & dr.columns(1).toString &
"&destination=" & dr.columns(2).toString
Next dr

.column(3).Commit
.tearDown
End With

generalDataSetQuery("postcodeafstand", "postcodeafstanden", "makeurl").tearDown

End Sub


When I modified dr.columns(3).value = "&origin=" &

dr.columns(1).toString & "&destination=" & dr.columns(2).toString

to dr.columns("makeurl").value = "&origin=" &
dr.columns("origin").toString & "&destination=" &
dr.columns("destination").toString
I got an error.
Executing the code above gives me no errors but does not show the
expected result in the column legs.distance.text.
Also the postalcodes from columns origin and destination disappear.
But I got the message 1 item retrieved. Unfortunately it
doesn't show up or gets killed by the last teardown in the code.























--
Graag ontvang ik een bevestiging retour.

Met vriendelijke groet,

Bas Neve
316 199 39 198

http://nl.linkedin.com/in/basneve

>>>>>> ... so its not possible to pick up multiple columns to generate a query. Is

> You received this message because you are subscribed to a topic in the

> Google Groups "Excel Liberation" group.

> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/excel-ramblings/EXUDGI4uTYo/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

[Bruce Mcpherson]

Hi

I finally got to a pc and tried this out. the code you need is below. I haven't seen your library entry yet, do if you still cant get it to work then let me see it.

Public Sub testPostcodeafstanden()

    Dim ds As New cDataSet, dr As cDataRow, sheetName As String, cName As String

    sheetName = "postcodeafstand"

    cName = "makeurl"

    

    ' create column if needed

    With ds.load(sheetName)

        If Not isSomething(.headingRow.exists(cName)) Then

            .headingRow.where.Resize(1, 1).Offset(, .columns.count).value = cName

        End If

        .tearDown

    End With

    ' now create the new values

    Set ds = New cDataSet

    With ds.load(sheetName)

        For Each dr In .rows

            dr.cell(cName).value = _

                "&origin=" & _

                dr.cell("origin").toString & _

                "&destination=" _

                & dr.cell("destination").toString

        Next dr

        .column(cName).Commit

        .tearDown

    End With

    'now do the query

    With restQuery(sheetName, sheetName, , cName, , , , , False)

        .tearDown

    End With

End Sub

However there is one small change you'll need to make to one of the classes so that it won't urlencode your parameter (its kind of a hack  to have embedded uri parameters in a query) 

- go to the crest class...

find this line

Set job = executeSingle(, URLEncode(dr.cell(pQueryhCell.toString).toString), , sFix)

and change it to this

Set job = executeSingle(, dr.cell(pQueryhCell.toString).toString, , sFix)

bruce

[Bas Neve]

Well I think I made an error 457 myself. The first time I run the
program it did not respond with this error.
But it did not gave me the required response.
Here is my libentry:

With .add("Postcodeafstanden")
.add "restType", erQueryPerRow
.add "url",
"https://maps.googleapis.com/maps/api/directions/json?key=Myapikey"
.add "results", "routes"

.add "treeSearch", True
.add "ignore", vbNullString

--
Graag ontvang ik een bevestiging retour.

Met vriendelijke groet,

Bas Neve
316 199 39 198

http://nl.linkedin.com/in/basneve

[Bruce Mcpherson]

ok.. here's the results

origin	destination	routes.1.legs.1.distance.value	status	makeurl
london	manchester	321563	OK	&origin=london&destination=manchester
3012 JR	1012 JS	73599	OK	&origin=3012 JR &destination=1012 JS

here's my entry

        With .add("postcodeafstand")

            .add "restType", erQueryPerRow

            .add "url", "https://maps.googleapis.com/maps/api/directions/json?mode=driving"

            .add "results", ""

            .add "treeSearch", False

            .add "ignore", vbNullString

        End With

I went one back so i could pick up the status as well.

Note that the directions API returns arrays - not single values.  both routes and legs are arrays - so the title i used for the column is the first  value of each of those arrays

[Bruce Mcpherson]

btw, you may want to post the problem and solution on the g+ community - https://plus.google.com/communities/103319333735778985706

im not really using this groups forum any more.

bruce

[Bas Neve]

Good morning Bruce,

I tried the whole week but I cannot get the same results as you.

Could you please have a look ?

Kind regards,

Bas

--

Graag ontvang ik een bevestiging retour.

Met vriendelijke groet,

Bas Neve
316 199 39 198

http://nl.linkedin.com/in/basneve

[Bruce Mcpherson]

looks like you have some data in the column headers beyond column 4 - by default ds.load() will use all the contingous columns it finds with data in them - its complaining that you have more than one column with the same heading. if you notice, cells e1:g1 have some unprintable characters in them. just delete them and it will work.

origin	destination	makeurl	routes.1.legs.1.distance.value

3012 JR

1012 JS

&origin=3012 JR&destination=1012 JS

73599