Making synchronous query to Google Sheet

Discussions and Tech Support specific to the iMacros Firefox add-on.
Forum rules
Before asking a question or reporting an issue:
1. Please review the list of FAQ's.
2. Use the search box (at the top of each forum page) to see if a similar problem or question has already been addressed.
3. Try searching the iMacros Wiki - it contains the complete iMacros reference as well as plenty of samples and tutorials.
4. We can respond much faster to your posts if you include the following information: CLICK HERE FOR IMPORTANT INFORMATION TO INCLUDE IN YOUR POST
Post Reply
lanx
Posts: 38
Joined: Sat Jul 03, 2010 2:11 am

Making synchronous query to Google Sheet

Post by lanx » Wed Dec 14, 2016 10:38 am

Hi folks!

I'm just facing a big trouble trying to query a simple Google Sheet because the following XMLHttpRequest passed through an EVAL command leads to a console error saying "XMLHttpRequest is not defined".

Code: Select all

SET !VAR0 EVAL("var url = \"https://docs.google.com/spreadsheets/d/***my-sheet-id-here***/gviz/tq?tq=select G1;out:csv\";GoogleQuery = new XMLHttpRequest();GoogleQuery.open(\"GET\", url, false);GoogleQuery.send(null);if (GoogleQuery.status === 200){GoogleResponse = GoogleQuery.responseText;evalresult = GoogleResponse};")
Since this javascript code is perfectly working as a function into a small standalone html/javascript application I have recently developed, I would like to know if there's a particular syntax to comply inside an iMacros EVAL command.

I would like only to query a specific cell and assign its value to an iMacros variable.

FCIM: Firefox 50.1.0 x64 running on Windows 10 [version 1607 x64, build 14393.576], iMacros 8.9.7
User avatar
alex686
Posts: 32
Joined: Wed Oct 21, 2015 6:48 am
Contact:

Re: Making synchronous query to Google Sheet

Post by alex686 » Fri Dec 16, 2016 6:03 am

There is no XmlHttpRequest constructor in iMacros sandboxed environment so you are getting this error. You must create an instance of it through XPCOM like this:

Code: Select all

var GoogleQuery = Components.classes['@mozilla.org/xmlextras/xmlhttprequest;1'].createInstance(Components.interfaces.nsIXMLHttpRequest);
iMacros for Firefox: 8.9.7
Firefox: 49.0.1
Pale Moon: 27.0.2
OS: Arch Linux, Windows 10

Manage iMacros from your mobile device: https://manage.getbot.ru
iimfun
Posts: 239
Joined: Tue Jul 19, 2016 1:06 pm

Re: Making synchronous query to Google Sheet

Post by iimfun » Tue Dec 20, 2016 8:12 am

Here is a possible solution for 'iim' macros (but of course not ideal one)

Code: Select all

SET url "https://docs.google.com/spreadsheets/d/***my-sheet-id-here***"
SET urlToFetch "{{url}}/gviz/tq?tq=select B2;out:csv"
TAB OPEN
TAB T=2
URL GOTO={{url}}
WAIT SECONDS=1
URL GOTO=javascript:(function(){GoogleQuery=new<SP>XMLHttpRequest();GoogleQuery.open("GET","{{urlToFetch}}",false);GoogleQuery.send(null);document.write(GoogleQuery.responseText);document.close();})();
WAIT SECONDS=3
SET !EXTRACT_TEST_POPUP NO
TAG POS=1 TYPE=* ATTR=* EXTRACT=TXT
TAB CLOSE
SET !VAR1 {{!EXTRACT}}
iimfun
Posts: 239
Joined: Tue Jul 19, 2016 1:06 pm

Re: Making synchronous query to Google Sheet

Post by iimfun » Tue Dec 20, 2016 8:15 am

Here is a possible solution for 'iim' macros (but of course not ideal one)

Code: Select all

SET url "https://docs.google.com/spreadsheets/d/***my-sheet-id-here***"
SET urlToFetch "{{url}}/gviz/tq?tq=select B2;out:csv"
TAB OPEN
TAB T=2
URL GOTO={{url}}
WAIT SECONDS=1
URL GOTO=javascript:(function(){GoogleQuery=new<SP>XMLHttpRequest();GoogleQuery.open("GET","{{urlToFetch}}",false);GoogleQuery.send(null);document.write(GoogleQuery.responseText);document.close();})();
WAIT SECONDS=3
SET !EXTRACT_TEST_POPUP NO
TAG POS=1 TYPE=* ATTR=* EXTRACT=TXT
TAB CLOSE
SET !VAR1 {{!EXTRACT}}
Post Reply