Google Sheets - API call from rows and update rows

Queries and Resources
1

I have spent a good 10 hours now on an issue and followed all the posts and articles that could relate, but still failing.

Summary

  • I have a simple google sheets with 4 columns in it. No issue in displaying the information.
  • I have an API trigger to external service. Trigger woks fine.
  • Using the records from the sheets, I need to make an API call for each row. I am successfully able to trigger the API call for each row, using the instructions from here:
    Scripting Retool 
var rows = getKeywords.data;
var errors = "";
var total = rows.length;

function runQuery(i) {
  // Update the statusText with the current progress
  statusText.setValue(i.toString() + "/" + total.toString());

  if (i >= rows.length) {
    console.log("Finished running all queries");
    return;
  }

  console.log("Running query for row", i);

  runAPIDataForSEO.trigger({
    additionalScope: {i: i}, // This is where we override the `i` variable from step 2!
    // You can use the argument to get the data with the onSuccess function
    
    onSuccess: function (data) { runQuery(i + 1); },
    onFailure: function (error) { errors += "Found error at line " + i.toString() + ":  " + error + "\n\n";
      errorText.setValue(errors);
      runQuery(i + 1);  },
  }),
    updateKGR.trigger();
}

runQuery(0);
statusText.setValue("");
errorText.setValue("");
  • From API response, I need to extract a field value and then update each sheets row that were used during the API call. Not working.

I created an update sheet query and added it to the js query that loops, but at this point I am failing to understand how to update back the sheet.

  • In Filter by, I am using a unique ID field and specified: {{getKeywords.data[i].ID}} which is the ID column of the sheet.
  • In Update value I am using { "Results":{{runAPIDataForSEO.data[i].tasks['0'].result['0'].se_results_count}}}
    Which is the object/key I get back from API response and that I need to use in my update.

I am stumped on what I need to do because no matter how many changes I made to the code, I am not able to update anything :frowning:

Thank you.

2

@ogmios

Hey there :wave: Thanks for reaching out! My first thought is that what may be happening here is that the update query is getting triggered before it has the API data. If you utilize Promises here, do you still run into the same issue?

3

Hi Lauren, I moved on from my attempt and had to hire someone to help. Thanks.