Adding a URL that has an api key to the restlibrary - Excel Liberation

The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Message from discussion Adding a URL that has an api key to the restlibrary
Received-SPF: neutral (google.com: 74.125.82.49 is neither permitted nor denied by best guess record for domain of br...@mcpher.com) client-ip=74.125.82.49;
MIME-Version: 1.0
In-Reply-To: <2b7d49ff-affb-44f4-bf49-80c24c4f4...@e15g2000vba.googlegroups.com>
References: <78a3dad1-20b2-4db6-9c95-295de7cb1...@e15g2000vba.googlegroups.com>
 <CANijH0Xm1KgsEuYiUsA2m=NUWvtdMs8rBvrdmjStsZcCW8T...@mail.gmail.com> <2b7d49ff-affb-44f4-bf49-80c24c4f4...@e15g2000vba.googlegroups.com>
From: Bruce Mcpherson <br...@mcpher.com>
Date: Wed, 9 May 2012 10:19:18 -0500
Message-ID: <CANijH0VTtsttU0QV5LjhMf1AW9KTOiYn6xVK1sBjEZFFhiE...@mail.gmail.com>
Subject: Re: [Excel Ramblings 267] Re: Adding a URL that has an api key to the restlibrary
To: excel-ramblings@googlegroups.com
Content-Type: multipart/alternative; boundary=e89a8f3ba5512ee94b04bf9c0851

--e89a8f3ba5512ee94b04bf9c0851
Content-Type: text/plain; charset=ISO-8859-1

Will

ok .. a couple of solutions.

   - you make the query include the key each time..
      -  so your url would just be http://api.klout.com/v2/user.json/
      - the query parameter each time would be <QUERY>/score?
      key=xxxx"
   - you dont bother with adding your term to the library, and just passed
   the complete URL to the crest class

bruce
2. you query parameter would include the key


On 9 May 2012 03:24, Will Hanschell <will.hansch...@gmail.com> wrote:

> Brilliant Bruce, thank you - this is such a great resource.
>
> Your advice makes total sense and works perfectly for the URL I
> supplied. However, my issue still applies for the second call I need
> to make.
>
> Klout requires that you call their API once to convert a Twitter
> screenName (the rows in my spreadsheet) to a Klout ID. (This is what
> you helped me with before.)
> You then call the API again to lookup a Klout score for that Klout ID.
> The URL for that is "http://api.klout.com/v2/user.json/<QUERY>/score?
> key=xxxx" where <QUERY> is the query supplied, and here it's
> hierarchical I believe so I can't just swap the terms?
>
> These are probably very simple questions but this is the final step in
> this hack! So close!
>
> Will
>
> On May 9, 12:06 am, Bruce Mcpherson <br...@mcpher.com> wrote:
> > Will
> >
> > I dont know if you noticed the rest explorer... you can use this to play
> > around with what your rest query returns, as well as play around with the
> > parameters and URL.
> >
> > http://ramblings.mcpher.com/Home/excelquirks/json/restexplore
> >
> > As I mentioned,
> > "http://api.klout.com/v2/identity.json/twitter?
> > screenName=xxxxx&key=xxxxx<
> http://api.klout.com/v2/identity.json/twitter?%0AscreenName=xxxxx&key...>
> > "
> >
> > ..should be able to become
> > "http://api.klout.com/v2/identity.json/twitter?
> > key=xxxxx<
> http://api.klout.com/v2/identity.json/twitter?%0AscreenName=xxxxx&key...>
> > &screenName=xxxxx<
> http://api.klout.com/v2/identity.json/twitter?%0AscreenName=xxxxx&key...>
> > "
> >
> > btw .. the queryPerRow you specify, performs multiple queries - one for
> > each row in your input data. Is that what you want ? Sounds like you want
> > to give one single query and get multiple answers...
> >
> > bruce
> >
> > On 8 May 2012 14:21, Will Hanschell <will.hansch...@gmail.com> wrote:
> >
> >
> >
> >
> >
> >
> >
> > > Hello all.
> >
> > > I'm trying to add the Klout API to the restlibrary and am having some
> > > difficulty. I think this is because the API's URL is a different
> > > structure to all those in the library already. Whereas with existing
> > > URLs, the query (in this case a Twitter screenName) is simply appended
> > > to the end of the URL when making the API call, with this URL the
> > > query needs to be inserted into the middle of the URL as the "key"
> > > must be placed after it. (see below)
> >
> > > Here's my library:
> >
> > >        With .add("klout id")
> > >            .add "restType", erQueryPerRow
> > >            .add "url", "http://api.klout.com/v2/identity.json/twitter?
> > > screenName=xxxxx&key=xxxxx<
> http://api.klout.com/v2/identity.json/twitter?%0AscreenName=xxxxx&key...>
> > > "
> > >            .add "id"
> > >            .add "treeSearch", True
> > >            .add "ignore", vbNullString
> > >        End With
> >
> > > I think that this whole issue is handled later on in the library - in
> > > the restquery function. Somewhere here:
> >
> > >  Set cr = New cRest
> > >    ' lets get the data
> > >    If (sheetName <> vbNullString) Then
> > >        Set dSet = New cDataSet
> > >        With dSet.populateData(wholeSheet(sheetName), ,
> > > sheetName, , , , True)
> > >            ' ensure that the query column exists if it was asked for
> > >            If qType = erQueryPerRow Then
> > >                If Not .headingRow.validate(True, sQueryColumn) Then
> > > Exit Function
> > >            End If
> > >            ' alsmost there
> > >            Set cr = cr.init(sResults,
> > > qType, .headingRow.exists(sQueryColumn), _
> > >                    , dSet, bPopulate, sUrl, bClearMissing, _
> > >                    bTreeSearch, complain, sIgnore)
> > >        End With
> >
> > > Does anybody have any ideas on this? Many hours so far and still
> > > scratching my head.
> >
> > > Thanks!
> >
> > > Will
> >
> > > --
> > > You received this message because you are subscribed to the Google
> Groups
> > > "Excel Ramblings" group.
> > > To post to this group, send email to excel-ramblings@googlegroups.com.
> > > To unsubscribe from this group, send email to
> > > excel-ramblings+unsubscribe@googlegroups.com.
> > > For more options, visit this group at
> > >http://groups.google.com/group/excel-ramblings?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Excel Ramblings" group.
> To post to this group, send email to excel-ramblings@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-ramblings+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/excel-ramblings?hl=en.
>
>

--e89a8f3ba5512ee94b04bf9c0851
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Will<br><br>ok .. a couple of solutions.<br><ul><li>you make the query incl=
ude the key each time..</li><ul><li>=A0so your url would just be<a href=3D"=
http://api.klout.com/v2/user.json/" target=3D"_blank"> http://api.klout.com=
/v2/user.json/</a></li>

<li>the query parameter each time would be &lt;QUERY&gt;/score?<br>
key=3Dxxxx&quot;</li></ul><li>you dont bother with adding your term to the =
library, and just passed the complete URL to the crest class</li></ul><p>br=
uce<br></p>2. you query parameter would include the key<br><br><br><div cla=
ss=3D"gmail_quote">

On 9 May 2012 03:24, Will Hanschell <span dir=3D"ltr">&lt;<a href=3D"mailto=
:will.hansch...@gmail.com" target=3D"_blank">will.hansch...@gmail.com</a>&g=
t;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0=
 .8ex;border-left:1px #ccc solid;padding-left:1ex">

Brilliant Bruce, thank you - this is such a great resource.<br>
<br>
Your advice makes total sense and works perfectly for the URL I<br>
supplied. However, my issue still applies for the second call I need<br>
to make.<br>
<br>
Klout requires that you call their API once to convert a Twitter<br>
screenName (the rows in my spreadsheet) to a Klout ID. (This is what<br>
you helped me with before.)<br>
You then call the API again to lookup a Klout score for that Klout ID.<br>
The URL for that is &quot;<a href=3D"http://api.klout.com/v2/user.json/" ta=
rget=3D"_blank">http://api.klout.com/v2/user.json/</a>&lt;QUERY&gt;/score?<=
br>
key=3Dxxxx&quot; where &lt;QUERY&gt; is the query supplied, and here it&#39=
;s<br>
hierarchical I believe so I can&#39;t just swap the terms?<br>
<br>
These are probably very simple questions but this is the final step in<br>
this hack! So close!<br>
<br>
Will<br>
<div class=3D"im"><br>
On May 9, 12:06=A0am, Bruce Mcpherson &lt;<a href=3D"mailto:br...@mcpher.co=
m">br...@mcpher.com</a>&gt; wrote:<br>
&gt; Will<br>
&gt;<br>
&gt; I dont know if you noticed the rest explorer... you can use this to pl=
ay<br>
&gt; around with what your rest query returns, as well as play around with =
the<br>
&gt; parameters and URL.<br>
&gt;<br>
&gt; <a href=3D"http://ramblings.mcpher.com/Home/excelquirks/json/restexplo=
re" target=3D"_blank">http://ramblings.mcpher.com/Home/excelquirks/json/res=
texplore</a><br>
&gt;<br>
&gt; As I mentioned,<br>
&gt; &quot;<a href=3D"http://api.klout.com/v2/identity.json/twitter" target=
=3D"_blank">http://api.klout.com/v2/identity.json/twitter</a>?<br>
</div>&gt; screenName=3Dxxxxx&amp;key=3Dxxxxx&lt;<a href=3D"http://api.klou=
t.com/v2/identity.json/twitter?%0AscreenName=3Dxxxxx&amp;key.." target=3D"_=
blank">http://api.klout.com/v2/identity.json/twitter?%0AscreenName=3Dxxxxx&=
amp;key..</a>.&gt;<br>


<div class=3D"im">&gt; &quot;<br>
&gt;<br>
&gt; ..should be able to become<br>
&gt; &quot;<a href=3D"http://api.klout.com/v2/identity.json/twitter" target=
=3D"_blank">http://api.klout.com/v2/identity.json/twitter</a>?<br>
</div>&gt; key=3Dxxxxx&lt;<a href=3D"http://api.klout.com/v2/identity.json/=
twitter?%0AscreenName=3Dxxxxx&amp;key.." target=3D"_blank">http://api.klout=
.com/v2/identity.json/twitter?%0AscreenName=3Dxxxxx&amp;key..</a>.&gt;<br>
&gt; &amp;screenName=3Dxxxxx&lt;<a href=3D"http://api.klout.com/v2/identity=
.json/twitter?%0AscreenName=3Dxxxxx&amp;key.." target=3D"_blank">http://api=
.klout.com/v2/identity.json/twitter?%0AscreenName=3Dxxxxx&amp;key..</a>.&gt=
;<br>


<div class=3D"im">&gt; &quot;<br>
&gt;<br>
&gt; btw .. the queryPerRow you specify, performs multiple queries - one fo=
r<br>
&gt; each row in your input data. Is that what you want ? Sounds like you w=
ant<br>
&gt; to give one single query and get multiple answers...<br>
&gt;<br>
&gt; bruce<br>
&gt;<br>
</div><div class=3D"im">&gt; On 8 May 2012 14:21, Will Hanschell &lt;<a hre=
f=3D"mailto:will.hansch...@gmail.com">will.hansch...@gmail.com</a>&gt; wrot=
e:<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt; &gt; Hello all.<br>
&gt;<br>
&gt; &gt; I&#39;m trying to add the Klout API to the restlibrary and am hav=
ing some<br>
&gt; &gt; difficulty. I think this is because the API&#39;s URL is a differ=
ent<br>
&gt; &gt; structure to all those in the library already. Whereas with exist=
ing<br>
&gt; &gt; URLs, the query (in this case a Twitter screenName) is simply app=
ended<br>
&gt; &gt; to the end of the URL when making the API call, with this URL the=
<br>
&gt; &gt; query needs to be inserted into the middle of the URL as the &quo=
t;key&quot;<br>
&gt; &gt; must be placed after it. (see below)<br>
&gt;<br>
&gt; &gt; Here&#39;s my library:<br>
&gt;<br>
&gt; &gt; =A0 =A0 =A0 =A0With .add(&quot;klout id&quot;)<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0.add &quot;restType&quot;, erQueryPerRow<b=
r>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0.add &quot;url&quot;, &quot;<a href=3D"htt=
p://api.klout.com/v2/identity.json/twitter" target=3D"_blank">http://api.kl=
out.com/v2/identity.json/twitter</a>?<br>
</div>&gt; &gt; screenName=3Dxxxxx&amp;key=3Dxxxxx&lt;<a href=3D"http://api=
.klout.com/v2/identity.json/twitter?%0AscreenName=3Dxxxxx&amp;key.." target=
=3D"_blank">http://api.klout.com/v2/identity.json/twitter?%0AscreenName=3Dx=
xxxx&amp;key..</a>.&gt;<br>


<div class=3D"HOEnZb"><div class=3D"h5">&gt; &gt; &quot;<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0.add &quot;id&quot;<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0.add &quot;treeSearch&quot;, True<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0.add &quot;ignore&quot;, vbNullString<br>
&gt; &gt; =A0 =A0 =A0 =A0End With<br>
&gt;<br>
&gt; &gt; I think that this whole issue is handled later on in the library =
- in<br>
&gt; &gt; the restquery function. Somewhere here:<br>
&gt;<br>
&gt; &gt; =A0Set cr =3D New cRest<br>
&gt; &gt; =A0 =A0&#39; lets get the data<br>
&gt; &gt; =A0 =A0If (sheetName &lt;&gt; vbNullString) Then<br>
&gt; &gt; =A0 =A0 =A0 =A0Set dSet =3D New cDataSet<br>
&gt; &gt; =A0 =A0 =A0 =A0With dSet.populateData(wholeSheet(sheetName), ,<br=
>
&gt; &gt; sheetName, , , , True)<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0&#39; ensure that the query column exists =
if it was asked for<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0If qType =3D erQueryPerRow Then<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0If Not .headingRow.validate(True, =
sQueryColumn) Then<br>
&gt; &gt; Exit Function<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0End If<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0&#39; alsmost there<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0Set cr =3D cr.init(sResults,<br>
&gt; &gt; qType, .headingRow.exists(sQueryColumn), _<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0, dSet, bPopulate, sUrl, b=
ClearMissing, _<br>
&gt; &gt; =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0bTreeSearch, complain, sIg=
nore)<br>
&gt; &gt; =A0 =A0 =A0 =A0End With<br>
&gt;<br>
&gt; &gt; Does anybody have any ideas on this? Many hours so far and still<=
br>
&gt; &gt; scratching my head.<br>
&gt;<br>
&gt; &gt; Thanks!<br>
&gt;<br>
&gt; &gt; Will<br>
&gt;<br>
&gt; &gt; --<br>
&gt; &gt; You received this message because you are subscribed to the Googl=
e Groups<br>
&gt; &gt; &quot;Excel Ramblings&quot; group.<br>
&gt; &gt; To post to this group, send email to <a href=3D"mailto:excel-ramb=
lings@googlegroups.com">excel-ramblings@googlegroups.com</a>.<br>
&gt; &gt; To unsubscribe from this group, send email to<br>
&gt; &gt; <a href=3D"mailto:excel-ramblings%2Bunsubscribe@googlegroups.com"=
>excel-ramblings+unsubscribe@googlegroups.com</a>.<br>
&gt; &gt; For more options, visit this group at<br>
&gt; &gt;<a href=3D"http://groups.google.com/group/excel-ramblings?hl=3Den"=
 target=3D"_blank">http://groups.google.com/group/excel-ramblings?hl=3Den</=
a>.<br>
<br>
--<br>
You received this message because you are subscribed to the Google Groups &=
quot;Excel Ramblings&quot; group.<br>
To post to this group, send email to <a href=3D"mailto:excel-ramblings@goog=
legroups.com">excel-ramblings@googlegroups.com</a>.<br>
To unsubscribe from this group, send email to <a href=3D"mailto:excel-rambl=
ings%2Bunsubscribe@googlegroups.com">excel-ramblings+unsubscribe@googlegrou=
ps.com</a>.<br>
For more options, visit this group at <a href=3D"http://groups.google.com/g=
roup/excel-ramblings?hl=3Den" target=3D"_blank">http://groups.google.com/gr=
oup/excel-ramblings?hl=3Den</a>.<br>
<br>
</div></div></blockquote></div><br>

--e89a8f3ba5512ee94b04bf9c0851--
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
Account Options
