Skip to main content
Solved

Automation scripting> only first record updated, rest skipped

A

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

Best answer by kuovonne

Query the view instead of the table.

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

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

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.

View original
Did this topic help you find an answer to your question?

5 replies

ScottWorld
Forum|alt.badge.img+20
  • ScottWorld
  • Brainy
  • 8743 replies
  • August 16, 2022

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
Forum|alt.badge.img+18

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?

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

Adam, Consultant @ The Time Saving Co
kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • 5995 replies
  • Answer
  • August 16, 2022

Query the view instead of the table.

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

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

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.

A
  • Anna_Orlova
  • Author
  • New Participant
  • 3 replies
  • August 16, 2022

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?

A
  • Anna_Orlova
  • Author
  • New Participant
  • 3 replies
  • August 16, 2022

@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:

kuovonne
J
2 people like this
  • kuovonne
    kuovonne
  • J
    Justin_Barrett

Reply


Most helpful members this week

Terms & Conditions