Hello, I'm using Drakma (CL) library to try and connect to Spreadsheets API (meaning I'm not using any of provided libraries). I'm facing a problem retrieving worksheetId. After I successfully authenticate and receive a list of spreadsheets, I don't see what is shown here: worksheet howto<http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html#WorksheetFeeds>, instead, I see a response very similar to what I've received when I sent the first request.
I'm posting the entire code and connection log with real sensitive data removed for obvious reason. Sorry, that's quite a bit of text, but maybe if you can spot an obvious error... Essentially, my problem is that when sending a request to GET https://spreadsheets.google.com/feeds/worksheets/key/private/full I receive the same response as if I've sent it to: GET https://spreadsheets.google.com/feeds/spreadsheets/private/full And here I'm stuck, because I'm not receiving a worksheetId, which I must have in order to retrieve a list feed... *sigh* Please, advise! Thank you in advance. *So, here go the logs:* * (get-spreadsheet "me" "my password") POST /accounts/ClientLogin HTTP/1.1 Host: www.google.com User-Agent: Drakma/1.2.3 (SBCL 1.0.29.11.debian; Linux; 2.6.32-30-generic; http://weitz.de/drakma/) Accept: */* Connection: close Content-Type: application/x-www-form-urlencoded Content-Length: 107 HTTP/1.1 200 OK Content-Type: text/plain Cache-control: no-cache, no-store Pragma: no-cache Expires: Mon, 01-Jan-1990 00:00:00 GMT Date: Thu, 24 Mar 2011 17:22:11 GMT X-Content-Type-Options: nosniff X-XSS-Protection: 1; mode=block Content-Length: 755 Server: GSE Connection: close GET /feeds/spreadsheets/private/full HTTP/1.1 Host: spreadsheets.google.com User-Agent: Drakma/1.2.3 (SBCL 1.0.29.11.debian; Linux; 2.6.32-30-generic; http://weitz.de/drakma/) Accept: */* Connection: close Authorization: GoogleLogin auth=xxxxxx HTTP/1.1 200 OK Content-Type: application/atom+xml; charset=UTF-8 Expires: Thu, 24 Mar 2011 17:22:11 GMT Date: Thu, 24 Mar 2011 17:22:11 GMT Cache-Control: private, max-age=0, must-revalidate, no-transform Vary: Accept, X-GData-Authorization, GData-Version GData-Version: 1.0 Last-Modified: Thu, 24 Mar 2011 17:22:11 GMT X-Content-Type-Options: nosniff X-Frame-Options: SAMEORIGIN X-XSS-Protection: 1; mode=block Server: GSE Connection: close >>> Found result URL: facebook interactive campaign_word bank >>> Requesting worksheet: https://spreadsheets.google.com/feeds/worksheets/***the-key***/private/full GET /feeds/spreadsheets/private/full HTTP/1.1 Host: spreadsheets.google.com User-Agent: Drakma/1.2.3 (SBCL 1.0.29.11.debian; Linux; 2.6.32-30-generic; http://weitz.de/drakma/) Accept: */* Connection: close Authorization: GoogleLogin auth=xxxxxx HTTP/1.1 200 OK Content-Type: application/atom+xml; charset=UTF-8 Expires: Thu, 24 Mar 2011 17:22:13 GMT Date: Thu, 24 Mar 2011 17:22:13 GMT Cache-Control: private, max-age=0, must-revalidate, no-transform Vary: Accept, X-GData-Authorization, GData-Version GData-Version: 1.0 Last-Modified: Thu, 24 Mar 2011 17:22:13 GMT X-Content-Type-Options: nosniff X-Frame-Options: SAMEORIGIN X-XSS-Protection: 1; mode=block Server: GSE Connection: close *And the code:* ; below is how far I could get with curl: ; curl -H 'Content-type: application/x-www-form-urlencoded' ; -d 'accountType=HOSTED_OR_GOOGLE&Email=***@rounds.com&Passwd=xxxxxxxx&service=wise&source=CL-spreadsheet-loader' ; https://www.google.com/accounts/ClientLogin ; curl -H 'Authorization: GoogleLogin auth=xxxxxxxx' ; https://spreadsheets.google.com/feeds/spreadsheets/private/full (require :drakma) (require :cxml) (setf drakma:*header-stream* *standard-output*) (defparameter *auth-service* "https://www.google.com/accounts/ClientLogin";) (defparameter *spreadsheets-full-service* "https://spreadsheets.google.com/feeds/spreadsheets/private/full";) ; entry[title.text() = "facebook interactive campaign_word bank"]/link[0].text() (defparameter *feed-title* "facebook interactive campaign_word bank") (defvar +auth-phrase+ "") (defun parse-token (response) (subseq response (+ (search "Auth=" response) 5) (length response)) ) ;; Need to url-encode arguments. (defun authorize-spreadsheet (username password) (let ((token)) (multiple-value-bind (response status) (drakma:http-request *auth-service* :method :post :content-type "application/x-www-form-urlencoded" :content (concatenate 'string "accountType=HOSTED_OR_GOOGLE&Email=" username "@rounds.com&Passwd=" password "&service=wise&source=CL-spreadsheet-loader") ) (when (= status 200) (setf token (parse-token response))) ) ) ) (defun node-matches (node search-name) (when (and node (string= search-name (dom:tag-name node)) ) t ) ) (defun title-equals (node title) (let ( (nodes (dom:child-nodes node)) (current-node) (current-text) (worksheet-url) (result) (current 0) ) (loop while (< current (length nodes)) do (progn (setf current-node (elt nodes current)) (when (and result (node-matches current-node "link")) (setf worksheet-url (dom:get-attribute current-node "href")) (setf result nil) ) (when (node-matches current-node "title") (setf current-text (dom:first-child current-node)) (when (string= (dom:node-value current-text) title) (format t "Found result URL: ~a~%" title) (setf result t) ) ) (setf current (+ current 1)) ) ) worksheet-url ) ) (defun request-worksheet (spreadsheet-url) (when spreadsheet-url (format t ">>> Requesting worksheet: ~a~%" spreadsheet-url) (multiple-value-bind (response) (drakma:http-request *spreadsheets-full-service* :method :get :additional-headers (list (cons "Authorization" +auth-phrase+)) :want-stream t ) (format t "~a~%" (cxml:parse-stream response (cxml:make-string-sink :indentation 2 :canonical nil)) ) ;(find-node (cxml:parse-stream response ; (cxml-dom:make-dom-builder)) ; "entry" ;) ) ) ) (defun find-node (document search-name) (let ( (nodes (dom:child-nodes (dom:document-element document))) (current 0) (node)) (loop while (< current (length nodes)) do (progn (setf node (elt nodes current)) (when (node-matches node search-name) (request-worksheet (title-equals node *feed-title*) ) ) (setq current (+ current 1)) ) ) ) ) (defun request-spreadsheets-list (token) (when token (setf +auth-phrase+ (concatenate 'string " GoogleLogin auth=" token)) (multiple-value-bind (response) (drakma:http-request *spreadsheets-full-service* :method :get :additional-headers (list (cons "Authorization" +auth-phrase+)) :want-stream t ) ;(format t "Status spreadsheet: ~a, headers: ~{~a~^,~}~%" status headers) ;(cxml:parse-stream response ; (cxml:make-string-sink :indentation 2 :canonical nil) ;) (find-node (cxml:parse-stream response (cxml-dom:make-dom-builder)) "entry" ) ) ) ) (defun get-spreadsheet (username password) (request-spreadsheets-list (authorize-spreadsheet username password)) )
