Calling an API based on data within cells A2:A100 and outputting to cells G2:G100-CodePudding

I've been trying to figure out how to get a Google AppsScript to pull in an API for keyword rank tracking directly within Google Sheets.

The loop is required to dynamically pull in information from column A and output the keyword ranking position into column G.

The keywords are in cells A2-A100. The ranking position (which is the only thing we are pulling from the API) we are popping into the corresponding row in column G, starting from G2. For testing purposes, we've got the loop set from 1 to 3.

We're at a bit of a loss as to why this isn't working as expected, and would really appreciate a nudge in the right direction!

The issue is that the very first result always returns 'keyword = undefined' within the API, and returning a result of '-1', meaning that the first row is not read. We've tried updating the r to 0, to 2, and changing the r references to no avail.

This makes us think that there must be something wrong with the loop, rather than the rest of the code, but please do correct me if this is not the case.

The script we've gotten so far is;

function callAPI() {
  //New loop
  for (r = 1; r <= 3; r  ) {
    {
      //Find keyword, encode query and url
      var query = keyword;
      var url =
        'https://api.avesapi.com/search?apikey={{APIKEYREMOVEDFORPRIVACY}}&type=web&'  
        'google_domain=google.co.uk&gl=gb&hl=en&device=mobile&output=json&num=100&tracked_domain={{CLIENTDOMAIN}}.com&position_only=true&uule2=London, United Kingdom'  
        '&query='  
        encodeURIComponent(query);

      //Call API and add to log
      var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      Logger.log(response);

      //Get column value for keyword
      var sheet = SpreadsheetApp.getActiveSheet();
      var keyword = sheet.getRange(1   r, 1).getValue();
    }

    //Set value of column
    var results = sheet.getRange(1   r, 7).setValue(response);
  }
}

Additional edit: So this is crystal clear, the desired input is;

keyword in A2 is read using the API and the output found (ranking position) is fed into G2.
the loop should then read A3, find the corresponding ranking position within the API, and adds that value to G3
rinse and repeat until the end of the loop.

Hopefully this is enough to go on, would really appreciate any advice, thank you!

CodePudding user response：

Basically from TheMaster's comments you switch up your statements to this:

function callAPI() {
  var sheet = SpreadsheetApp.getActiveSheet();

  //New loop
  for (r = 1; r <= 3; r  ) {
    {
      //Get column value for keyword
      var keyword = sheet.getRange(1   r, 1).getValue();

      //Find keyword, encode query and url
      var query = keyword;
      var url =
        'https://api.avesapi.com/search?apikey={{APIKEYREMOVEDFORPRIVACY}}&type=web&'  
        'google_domain=google.co.uk&gl=gb&hl=en&device=mobile&output=json&num=100&tracked_domain={{CLIENTDOMAIN}}.com&position_only=true&uule2=London, United Kingdom'  
        '&query='  
        encodeURIComponent(query);

      //Call API and add to log
      var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      Logger.log(response);

    }

    //Set value of column
    var results = sheet.getRange(1   r, 7).setValue(response);
  }
}

Note that I moved the sheet declaration outside the loop, it needs to be only called once.