2

I'm trying to create a simple custom function using the Genderize API in Google Sheets using Google Apps Script, but I keep getting a request limit reached 429 error from the genderize API. I'm sure that I haven't exceeded the limit of 1000 calls.

I'm also a complete beginner, so perhaps there are a few mistakes in my code. Any help would be greatly appreciated! 

    function fetch_(query) {
    var response = UrlFetchApp.fetch("https://api.genderize.io/?name=" + encodeURI(query.toLowerCase()));
    Logger.log(response.getContentText());
    Utilities.sleep(1000);
    var out = JSON.parse(response.getContentText());
    return out
    }

    /**
     * Derive gender based on a first name using the Genderize API.
     *
     * This formula helps retrieve the gender of a given first name.
     *
     * For example:
     *
     *   =GetGender("juan").
     * Developped by somejuan.dk
     *
     * @param query The name of the contact.
     *
     * @return The gender of the name submitted.
     * @customfunction
     **/
    function GetGender(query) {
      var json_out = fetch_(query) ;
      return json_out['gender'];
      }

Error message is: Request failed for https://api.genderize.io/?name=juan returned code 429.

Truncated server response: {"error":"Request limit reached"} (use muteHttpExceptions option to examine full response). (line 6).

Rubén
  • 34,714
  • 9
  • 70
  • 166
Somejuan
  • 21
  • 3
  • 1
    Could you please provide us with the code that forms query / calls the `GetGender` function (btw, naming convention of functions that are not class constructors is to start with lowercase)? And a small off-topic - why do you call `sleep` method? – Oleg Valter is with Ukraine Jul 31 '19 at 13:13
  • 1
    1000 names/day is the genderize.io limit, but you could also have surpassed the Apps Script quota. Please check Apps Script quotas here: https://developers.google.com/apps-script/guides/services/quotas If you did not exceed the daily limits of maximum URL fetch calls, you could have reached another limit, like maximum e.g. script runtime/execution. It is also possible that your maximum available quota has been limited manually inside the Google Developers or Google Cloud Console https://console.cloud.google.com – ziganotschka Jul 31 '19 at 13:56
  • 4
    Genderize receives the request from Google servers. If someone else is also using apps script with genderize, there is no way (or no way provided by genderize) to differentiate both requests as requests from different users. Also, You can't change the user agent of GAS. So, I don't think your problem can be solved without support from Genderize(or Google, but convincing Google is impossible) – TheMaster Jul 31 '19 at 15:45
  • @OlegValter Hi, the idea is that it pulls a cell containing a first name. Hence no query is specified in the code. Since Genderize only accepts lowercase request, i used the lowerCase function to format the query prior to sending the request. I'm not too familiar with the sleep method but the idea was to pause execution when bulk requesting so it didnt break. But as mentioned i'm very much in the beginners phase, i appreciate your comment! – Somejuan Aug 01 '19 at 07:25
  • @ziganotschka I checked but I had definitely not reached the app scripts quota, but I didn't know about this quota either, thank you very much for pointing this out. Also I couldn't find out how to check if it had been manually limited... thank you for your comment! – Somejuan Aug 01 '19 at 07:28
  • @TheMaster I believe you have got the right answer. I did not think of this. I guess that makes my script pretty useless. Thank you so much. However I tried using the importdata function, and suddenly it stopped being able to fetch data from genderize. Do you think that this is the same case? – Somejuan Aug 01 '19 at 07:30
  • @Somejuan. The real issue is the one pointed out by TheMaster, I am trying to find a workaround for you. – ziganotschka Aug 01 '19 at 07:31
  • @ziganotschka thank you so much for your time. And yeah I think that is an issue that kind of kills the purpose of my script. But I'm always glad to learn something new! – Somejuan Aug 01 '19 at 07:34
  • If you think it kills the purpose of your script, then it might not be worth using Apps Script for it. Because the solution I was thinking of was deploying Apps Script as a Web App, and fetch the request URL client-side from an iframe embedded in the html output of the Web App. This request will be performed with your own IP and will surpass the maximum "Apps Script quota". However, it is a little bit tricky and laborous to implement. – ziganotschka Aug 01 '19 at 07:44
  • @Somejuan Yes, I think that's the same issue with importdata. You might want to contact genderize to see if they have a workaround/free api key. Script isn't useless - It can be used with paid api keys. – TheMaster Aug 01 '19 at 08:05
  • @Somejuan, sorry just saw your response - I think TheMaster and ziganotschka pretty much answered the question, so just a small comment regarding my inquiry: 1. How many requests to the API you usually make? From the discussion, I am not sure if you are able to make at least some queries or none at all; 2. I only meant `GetGender` -> `getGender` (usually such named functions are used to signify class constructors, at least prior to `class`); 3. Thanks for clarifying - as there was no loop or reference to a query source, I could not understand the purpose of making request to wait – Oleg Valter is with Ukraine Aug 01 '19 at 10:37

0 Answers0