Automation scripting> only first record updated, rest skipped

[Anna_Orlova]

Hello,

I made a script to query a API and update current rankings of records. I have a test table with 5 records and 1 is a empty record.
See screenshot for my test table:

code:

//set the variables
let api_key = '{apikey}';
let table = base.getTable('tesdata');
let query = await table.selectRecordsAsync({fields: table.fields});

for (let record of query.records) {
let domain = record.getCellValueAsString('Website');
let zoekterm = record.getCellValue('Zoekterm');
let country = record.getCellValue('Land');
let gl=country;
let hl=gl;
 if(gl=='co.uk'){
    gl='uk';
    hl='en';
    }
    else if (gl=='com'){
        gl = 'us';
        hl = 'en';
      }
//build the api call variables
var url = 'https://serpapi.com/search?q='+encodeURIComponent(zoekterm)+ '&gl='+gl+'&hl='+hl+'&output=rank:'+domain+'&google_domain=google.'+country+'&num=100'
      + '&api_key=' + api_key;
console.log(url);
var requestOptions = {
  method: 'GET',
};
//build the api call variables
//make api call
try{
var response= await fetch(url,requestOptions);
//make api call
const data = await response.json();
console.log(data)
  await table.updateRecordAsync(record, {
        // Change these names to fields in your base
        'Hpos': data,
    });
}catch(e){}
}

I have 2 issues with the script:
1)
The script works fine and returns all positions if I do console.log(data) without updating the actual Hpos fields.
However, if I add the update Hpos lines, script stops at the first row.
I need the script to update all rows that yield a complete query.

the table records seem to be loaded and processed in arbitrary mode. How can I make sure it follows the order of the tble displayed?
Record.id does not indicate the order.

What is wrong here and how to fix?

Thank you so much for troubleshooting with me.

-Anna

[ScottWorld]

Sorry, I don’t know JavaScript, so I can’t help you there.

But since you’re querying an external API, you may just want to use the Data Fetcher extension.

[TheTimeSavingCo]

Anna_Orlova:

The script works fine and returns all positions if I do console.log(data) without updating the actual Hpos fields.
However, if I add the update Hpos lines, script stops at the first row.
I need the script to update all rows that yield a complete query.

Hi Anna, does anything print when you put a console log into your catch? Maybe there’s an error and so it’s stopping there?

Anna_Orlova:

the table records seem to be loaded and processed in arbitrary mode. How can I make sure it follows the order of the tble displayed?
Record.id does not indicate the order.

What is wrong here and how to fix?

If you’re referring to the sort order here, you’ll need to modify your selectRecordsAsync to sort as well. You can find the documentation for that here

[kuovonne · Accepted solution]

Anna_Orlova:

the table records seem to be loaded and processed in arbitrary mode. How can I make sure it follows the order of the tble displayed?

Query the view instead of the table.

let view = table.getView("name of view")

let query = await view.selectRecordsAsync({fields: table.fields});

Anna_Orlova:

However, if I add the update Hpos lines, script stops at the first row.
I need the script to update all rows that yield a complete query.

Does the script stop completely or does it simply not update the additional records? Does console.log() produce anything for the remaining records? Can’t you provide screen captures? What is the field type of the “Hpos” field?

It may be that the value for data is not compatible with the field type, but your try/catch is hiding any errors. I suggest removing the try/catch or at least logging the error in the catch.

[Anna_Orlova]

Hello,

@Adam_TheTimeSavingCo
Ok, I added the console.log and get as error, very criptical for me:

CONSOLE.LOG

1. :arrow_forward: Error {name: “d”}

2. name: “d”

@kuovonne OK! I will check what will happen if I query view asap.
About the error: it gives me: CONSOLE.LOG

1. :arrow_forward: Error {name: “d”}

2. name: “d”

Data type of the field is number. And it accepts the first position returned by the api, thus I do not think the format is the issue?

[Anna_Orlova]

@kuovonne Actually, after changing the script to pull data from view like below, both issues were solved! woop!:

let table = base.getTable("tesdata");
let view = table.getView("Grid view");
let query = await view.selectRecordsAsync({fields: table.fields});

Result: