Sometimes, Supermetrics only returns errors throughout the entire spreadsheet... Need help

[Geoffrey Liu]

I have no idea what causes this to occur, but sometimes, Supermetrics decides to only return errors and no relevant data whatsoever. Here is a screenshot of the occurrence: http://i.imgur.com/oXidOUO.png

All of the errors have the same message: ReferenceError: "includeHeaders" is not defined. And from what I can tell, it has nothing to do with that variable not being defined because Supermetrics will return perfectly valid data one minute, and then the error the next minute. All this happens without me even touching the script / /code.

Can someone tell me why this keeps happening?

[Mikael Thuneberg]

Thanks for reporting the issue. It seems to be a bug in our code. You should be able to fix this by going to Tools: Script Editor, deleting the script already there, and pasting code below there instead, and pressing Save.

function getVersionNumber(){return versionNumber}function getGAaccountData(e,t,n){return getAccountData(e,t,n)}function getAccountData(e,t,n){if(typeof e=="undefined"){return"Authentication token missing, get from one supermetrics.com/functions-login"}if(e.length==0){return"Authentication token missing, get from one supermetrics.com/functions-login"}if(e.substr(0,5)!="pwfat"){return"Authentication token missing, get from one supermetrics.com/functions-login"}t=typeof t=="undefined"?"profiles":t;t=t.length==0?"profiles":t;n=typeof n=="undefined"?true:n;var r=CacheService.getPublicCache();var i=e+t;var s=Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5,i));var o;var u=r.get(s);var a=true;var f="https://supermetrics.com/ga/accountData.php";if(u==null){a=false;t=t.toLowerCase();o="token="+encodeURIComponent(e);o+="&datatype="+t;o+="&version="+versionNumber;o+="&system=GoogleDocs";o+="&rscL0="+encodeURIComponent(rscL0);o+="&rscL1="+encodeURIComponent(rscL1);o+="&rscL2="+encodeURIComponent(rscL2);o+="&rscL3="+encodeURIComponent(rscL3);o+="&rscL4="+encodeURIComponent(rscL4);if(n){o+="&includeHeaders=1"}else{o+="&includeHeaders=0"}try{var l=UrlFetchApp.fetch(f,{method:"post",payload:o,validateHttpsCertificates:false,muteHttpExeptions:true})}catch(c){if(c.message.indexOf("time")!=-1){randnumber=Math.random()*1e4;Utilities.sleep(randnumber);return getAccountData(e,t,n)}return"Account fetch error: "+c}u=l.getContentText()}if(u.substr(0,7)!="SUCCESS"){return"Error: "+u}try{r.put(s,u,3600)}catch(c){}u=u.substr(8);var h=u.split(rscL2);var p=h[0].split(rscL3);var d=h[1].split(rscL3);if(t!="segments"){var v=h[2].split(rscL3)}rowCount=p.length+1;if(rowCount==0){return"Error: No data found"}var h=[];var m=[];var g=0;if(n){if(t=="segments"){m[0]=replaceRSCL(d[0]);m[1]=replaceRSCL(p[0])}else{m[0]=replaceRSCL(p[0]);m[1]=replaceRSCL(d[0]);m[2]=replaceRSCL(v[0])}h[0]=m;g=1}m=[];for(var y=1;y<=p.length;y++){if(t=="segments"){m[0]=replaceRSCL(d[y]);m[1]=replaceRSCL(p[y])}else{m[0]=replaceRSCL(p[y]);m[1]=replaceRSCL(d[y]);m[2]=replaceRSCL(v[y])}h[g]=m;g++;m=[]}return h}function getGAdata(e,t,n,r,i,s,o,u,a,f,l,c,h,p,d){return getData(e,t,n,r,i,s,o,u,a,f,l,c,h,p,d)}function getData(e,t,n,r,i,s,o,u,a,f,l,c,h,p,d){p=typeof p=="undefined"?"":p;if(f){p+=",INCLUDE_HEADERS"}else{p+=",NO_HEADERS"}if(!l){p+=",NO_DIMENSIONS"}return Supermetrics(e,t,n,r,i,o,h,s,u,a,p,c,d)}function Supermetrics(e,t,n,r,i,s,o,u,a,f,l,c,h){var p="";var d="";try{r.getYear()}catch(v){p=r}try{i.getYear()}catch(v){d=i}if(typeof e=="undefined"){return"Authentication token missing, get from one supermetrics.com/functions-login"}if(e.length==0){return"Authentication token missing, get from one supermetrics.com/functions-login"}if(e.substr(0,5)!="pwfat"){return"Authentication token missing, get from one supermetrics.com/functions-login"}if(typeof t=="undefined"||t.length==0){return"Profile ID missing"}if(typeof n=="undefined"||n.length==0){return"Metrics missing"}u=typeof u=="undefined"?"":u;s=typeof s=="undefined"?"":s;o=typeof o=="undefined"?"":o;a=typeof a=="undefined"?"":a;f=typeof f=="undefined"?false:f;c=typeof c=="undefined"?100:c;h=typeof h=="undefined"?10:h;l=typeof l=="undefined"?"":l;if(f==true){f="metricdesc"}else if(f==false){f="dimensionasc"}var m;var g=CacheService.getPublicCache();var y=e+t+n+r+i+u+s+a+f+l+o+c+h;var b=Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5,y));m=g.get(b);var w=true;if(m==null){w=false;if(p==""){try{var E=r.getFullYear().toString();var S=(r.getMonth()+1).toString();var x=r.getDate().toString();p=E+"-"+(S[1]?S:"0"+S[0])+"-"+(x[1]?x:"0"+x[0])}catch(v){}}if(d==""){try{var E=i.getFullYear().toString();var S=(i.getMonth()+1).toString();var x=i.getDate().toString();d=E+"-"+(S[1]?S:"0"+S[0])+"-"+(x[1]?x:"0"+x[0])}catch(v){}}t=t.toString().replace("&",rscL1).replace(",",rscL1);n=n.replace("&",rscL1).replace(",",rscL1);s=s.replace("&",rscL1).replace(",",rscL1);if(o!=""){o=o.replace("&",rscL1).replace(",",rscL1);o=o.replace(rscL1,"_pivot"+rscL1)+"_pivot";s+=rscL1+o;if(l.indexOf("NO_HEADERS")==-1&&l.indexOf("INCLUDE_HEADERS")==-1){l+=",INCLUDE_HEADERS"}}var T="https://supermetrics.com/ga/main.php";var N="token="+encodeURIComponent(e);N+="&version="+encodeURIComponent(versionNumber);N+="&system=GoogleDocs";N+="&rscL0="+encodeURIComponent(rscL0);N+="&rscL1="+encodeURIComponent(rscL1);N+="&rscL2="+encodeURIComponent(rscL2);N+="&rscL3="+encodeURIComponent(rscL3);N+="&rscL4="+encodeURIComponent(rscL4);N+="&profiles="+encodeURIComponent(t);N+="&metrics="+encodeURIComponent(n);N+="&dimensions="+encodeURIComponent(s);N+="&filterStr="+encodeURIComponent(u);N+="&segment="+encodeURIComponent(a);N+="&dateRangeType=fixed";N+="&start-date="+encodeURIComponent(p);N+="&end-date="+encodeURIComponent(d);N+="&sort="+encodeURIComponent(f);N+="&segment="+encodeURIComponent(a);N+="&maxResultsAll="+encodeURIComponent(c);N+="&settings="+encodeURIComponent(l);N+="&pivotDimensions="+encodeURIComponent(o);N+="&maxCategories="+encodeURIComponent(h);N+="&returnQueryIDafterSeconds=0";try{var C=Math.random()*5e3;Utilities.sleep(C);var k=UrlFetchApp.fetch(T,{method:"post",payload:N,validateHttpsCertificates:false,muteHttpExeptions:true});m=k.getContentText()}catch(v){if(v.message.indexOf("time")!=-1){C=Math.random()*1e4;Utilities.sleep(C);return Supermetrics(e,t,n,r,i,s,o,u,a,f,l,c,h)}return"Fetching data failed ("+v.message+")"}var L=parseVarFromStr(m,"queryIDforDB",rscL2);if(L!=""){m="";var A=0;do{C=Math.random()*2e3;Utilities.sleep(C);A++;try{var O=UrlFetchApp.fetch("https://supermetrics.com/ga/getQueryResults.php",{method:"POST",payload:"queryIDforDB="+L+"&acceptStatus=TRUE&rscL0="+rscL0+"&waitUpTo=50",muteHttpExeptions:true});m=parseVarFromStr(O.getContentText(),"RESULTS",rscL0)}catch(v){if(v.message.indexOf("time")!=-1){C=Math.random()*1e4;Utilities.sleep(C)}}}while(m==""&&A<200)}}var M=m.split(rscL1)[0];if(parseVarFromStr(M,"STATUS",rscL2)=="ERROR"){return"Error: "+parseVarFromStr(m,"ERROR",rscL2)}try{g.put(b,m,3600)}catch(v){}var _=parseInt(parseVarFromStr(M,"NUMERIC_FORMAT_COLUMNS_START",rscL2));var D=parseInt(parseVarFromStr(M,"NUMERIC_FORMAT_ROWS_START",rscL2));var P;if(parseVarFromStr(M,"CONVERT_RSCL",rscL3)=="TRUE"){P=true}else{P=false}var H=m.split(rscL1)[1].split(rscL2);var B=m.split(rscL1)[2].split(rscL2);var j=H.length;var F=B.length;if(j==0||F==0){return"Error: No data found"}var I=[];for(var q=0;q<F;q++){I[q]=B[q].split(rscL3)}for(q=0;q<D;q++){for(var R=0;R<j;R++){I[q][R]=replaceRSCL(I[q][R])}}for(q=0;q<F;q++){for(var R=0;R<_;R++){I[q][R]=replaceRSCL(I[q][R])}}for(q=D;q<F;q++){for(var R=_;R<j;R++){I[q][R]=Number(I[q][R])}}return I}function getTweets(e,t,n,r,i,s,o,u,a,f,l){var c;var h;var p;if(typeof e=="undefined"||e==""){return"Authentication token missing, get from one supermetrics.com/functions-login"}if(e(0,5)!="pwfat"){return"Authentication token invalid, get from one supermetrics.com/functions-login"}if(typeof t=="undefined"||t==""){return"Please enter a search term"}n=n==undefined?"time,twitter_name,tweet":n;r=r==undefined?100:r;i=i==undefined?true:i;s=s==undefined?"recent":s;l=l==undefined?"UTC":l;c="https://supermetrics.com/twitter/getTweets.php";h="q="+encodeURIComponent(t);h+="&token="+encodeURIComponent(e);h+="&dataSource=TW";h+="&format=JSON";h+="&columns="+encodeURIComponent(n);h+="&maxResults="+r;h+="&result_type="+s;h+="&timezone="+encodeURIComponent(l);if(o!=undefined){h+="&lang="+o}if(f!=undefined){h+="&locale="+f}if(a!=undefined){h+="&until="+encodeURIComponent(a)}if(u!=undefined){h+="&geocode="+encodeURIComponent(u)}if(o!=undefined){h+="&lang="+o}if(i){h+="&includeHeaders=1"}else{h+="&includeHeaders=0"}var d=CacheService.getPublicCache();var v=h;var m=Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5,v));var g=d.get(m);var y;if(g==null){y=false;var b=Math.random()*5e3;Utilities.sleep(b);try{var w=UrlFetchApp.fetch(c,{method:"post",payload:h,validateHttpsCertificates:false,muteHttpExeptions:true})}catch(E){if(E.message.indexOf("time")!=-1){b=Math.random()*1e4;Utilities.sleep(b);return getTweets(e,t,n,r,i,s,o,u,a,f,l)}return"Account fetch error: "+E}g=w.getContentText();g=g.replace(/(\r\n|\n|\r)/gm,"")}else{y=true}p=JSON.parse(g);if(!y&&p.ERROR==undefined){try{d.put(m,g,15*60)}catch(E){}}return p.data}function parseVarFromStr(e,t,n){var r=e.indexOf(n+t);if(r==-1){return""}else{var i=e.substr(r+(n+t+"->").length);return i.substr(0,i.indexOf(n))}}function replaceRSCL(e){if(typeof e=="undefined"){return e}e=e.replace(/%rscL1%/g,rscL1);e=e.replace(/%rscL2%/g,rscL2);e=e.replace(/%rscL3%/g,rscL3);return e}var versionNumber="2.31";var rscL0="$";var rscL1="#";var rscL2="`";var rscL3="^";var rscL4="~"

--
You received this message because you are subscribed to the Google Groups "Supermetrics" group.
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalyt...@googlegroups.com.
Visit this group at http://groups.google.com/group/automateanalytics.
To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/532ff270-c757-4e30-bf73-d83ed3c4b3f0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Geoffrey Liu]

Thank you for the quick reply. I will let you know if the issue ever occurs again.

[Geoffrey Liu]

Ok, so I don't seem to get the includeHeaders error anymore, but now, after a while, I still get a page full of errors. The errors are different this time, and now they say "Internal error executing the custom function". Here is an example picture.

It says here on Google's developer page that "A custom function call must return within 30 seconds. If it does not, the cell will display an error: Internal error executing the custom function." I commenting out some of the Utilities.sleep() function calls in the Supermetrics code, but that didn't seem to have any effect. I've also read on some message boards for Supermetrics that the sleep functions are needed to avoid exceeding the call per minute quote imposed by Google Apps scripts.

Any help would be greatly appreciated.

On Thursday, August 28, 2014 2:40:50 AM UTC-4, Supermetrics Support wrote:

[Mikael Thuneberg]

That can happen if you have too many queries in one spreadsheet. You should fetch as much data as possible with a single query, and then use lookup functions to extract the individual values from the results.

To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/07811145-cb01-4394-a1eb-b009c3c38808%40googlegroups.com.

[Geoffrey Liu]

I followed your advice and tried to reduce the number of queries as much as possible, and it seems to have improved the overall performance of the queries and the response time. However, I am still getting the error output after a while. I have noticed that it only seems to start occurring after extensive querying. For example, at the beginning of the day, I can query as much as I want, and it all responses are returned within a reasonable amount of time. After maybe a few hours, all the queries simply return the aforementioned errors. Even if I reduce the number of queries in the entire spreadsheet to one query, I still get the internal server error.

Is there any other reason this could be happening?

[Mikael Thuneberg]

Maybe Google has a daily limit which you are exceeding. Unfortunately there's nothing we can do about this, you just have to try to reduce the number of queries further. The other option is to use our GDocs add-on (supermetrics.com/gdocs) instead of the functions. With the add-on, we can do more things to get around Google's limits.

To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/32389f8d-8b16-43b7-b891-e65efa261a90%40googlegroups.com.