loop over google map api pages in google script

Question

i am trying to get data from google map api and put them in google sheets, i created a loop

to get data from next token page.

first function is to get location from cell in sheet

second function is to get the first page and loop to next pages

third function if to loop in next pages

but seems their is a problem in third function maybe cause fetch is not reading the Url i dont know why

results: i got first 20 row then the code give (Exception: The number of rows in the range must be at least 1.)

can someone give me some hints or correct what i did wrong please ? thanks in advance

function APIfromLoc(){
  var ss = SpreadsheetApp.openById('1kiecwnIEHNvTz3VD8AjPT0CK8HvqmzCi86vXRyrU4lc');
  var sheet = ss.getSheetByName('Sheet1');

  var location = sheet.getRange(1,1).getValue();

  callAPIinit(location);

}

// function to call the first page api 
function callAPIinit(location) {
  var ss = SpreadsheetApp.openById('1kiecwnIEHNvTz3VD8AjPT0CK8HvqmzCi86vXRyrU4lc');
  var sheet = ss.getSheetByName('Sheet2');

  var API_key = '';
  //var location = ' ' ;
  var Url = 'https://maps.googleapis.com/maps/api/place/textsearch/json?location='+location+'&raduis=50000&type=restaurant&key='+API_key;

  var response = UrlFetchApp.fetch(Url);



  // Parse the JSON reply
  var json = response.getContentText();
  var data = JSON.parse(json);


  var results = data["results"];

  var token = data["next_page_token"];

  var output = []

  results.forEach(function(elem,i) {

    output.push([elem["formatted_address"],elem["geometry"]["location"]["lat"],elem["geometry"]["location"]["lng"],elem["geometry"]["viewport"]["northeast"]["lat"],elem["geometry"]["viewport"]["northeast"]["lng"],elem["geometry"]["viewport"]["southwest"]["lat"],elem["geometry"]["viewport"]["southwest"]["lng"],elem["id"],elem["name"],elem["place_id"],elem["plus_code"]["compound_code"],elem["plus_code"]["global_code"],elem["rating"],elem["reference"],elem["types"][0],elem["types"][1],elem["types"][2],elem["types"][3],elem["price_level"],elem["user_ratings_total"]]);
   // sheet.setRowHeight(i,65);
  });

    // adds an index number to the array
  output.forEach(function(elem,i) {
    elem.unshift(i + 1);
  });

  var len = output.length;


  // paste in the values
  sheet.getRange(2,1,len,21).setValues(output);
  sheet.getRange(1,1).setValue(token);

 Next_pages(Url);
}

/**
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 */

// function to call the next pages using first page token 
function Next_pages(Url) {


  var ss = SpreadsheetApp.openById('1kiecwnIEHNvTz3VD8AjPT0CK8HvqmzCi86vXRyrU4lc');
  var sheet = ss.getSheetByName('Sheet2');
  var token = sheet.getRange(1,1).getValue();
  Url = Url + "&pagetoken=" + token;
 Logger.log(Url)
 //loop to get all pages  
  do{

  //var location = ' ' ;
 // var Url = Url+'&pagetoken='+token;

  var response = UrlFetchApp.fetch(Url);
  var json = response.getContentText();

  var data = JSON.parse(json);
  Logger.log(data)
  var results = data["results"];
  token = []; 
  token = data["next_page_token"];

  var output = []

  results.forEach(function(elem,i) {

    output.push([elem["formatted_address"],elem["geometry"]["location"]["lat"],elem["geometry"]["location"]["lng"],elem["geometry"]["viewport"]["northeast"]["lat"],elem["geometry"]["viewport"]["northeast"]["lng"],elem["geometry"]["viewport"]["southwest"]["lat"],elem["geometry"]["viewport"]["southwest"]["lng"],elem["id"],elem["name"],elem["place_id"],elem["plus_code"]["compound_code"],elem["plus_code"]["global_code"],elem["rating"],elem["reference"],elem["types"][0],elem["types"][1],elem["types"][2],elem["types"][3],elem["price_level"],elem["user_ratings_total"]]);
   // sheet.setRowHeight(i,65);
  });

    // adds an index number to the array
  output.forEach(function(elem,i) {
    elem.unshift(i + 1);
  });

  var len = output.length;

  // paste in the values
  sheet.getRange(sheet.getLastRow()+1,1,len,21).setValues(output);
  sheet.getRange(1,1).clearContent();

} while(token != Null)
}

score 1 · Answer 1 · answered Apr 15 '20 at 09:50

Most probably you are reaching quota usage limits because you are trying to fetch too many locations. That's what I got at least, when trying to reproduce this behaviour.

When the quota is reached, instead of returning the list of locations, the API returns this error:

{error_message=You have exceeded your daily request quota for this API. If you did not set a custom daily request quota, verify your project has an active billing account: http://g.co/dev/maps-no-account, status=OVER_QUERY_LIMIT, results=[], html_attributions=[]}

If you notice this error response, the results field is an empty array. As a result, output is an empty array too, and so output.length; is 0. You are then trying to get a range, with a number of rows (len) equal to 0, which explains the error you are getting.

In order to view your current quota limits, you can follow these steps.

Note:

There is a typo in your URL: raduis is not a valid query parameter; should be radius instead.

Reference:

Places API: Quota and Reporting

score 0 · Answer 2 · answered Apr 16 '20 at 11:32

0

thanks for your help, seems like next page token need some time to appear so i added Utilities.sleep(10000) to the next pages loop.

reference Paging on Google Places API returns status INVALID_REQUEST

answered Apr 16 '20 at 11:32

Hadi Hobballah

21
2

loop over google map api pages in google script

2 Answers2

Note:

Reference: