Completely new to Airtable - help needed with PHP echo

[Bizarrebra]

Hello,

I’m originally a programmer but I’m completely new to Airtable and the script syntax. Basically, I have a PHP file somewhere in my server that makes a GET request to Twitch and gets the follower count of any channel.

Now, I have in Airtable a list of Twitch users, with their username. What I want do to is to pass the username to my URL so that the PHP script in my server does the rest, and, posts a simple ECHO with the number of followers. Then I’ll just grab the number to update my field. A bit more graphically:

1. Grab the ‘username’ field from my table ‘table’.
2. Append it to my external URL, i.e. “https://mycoolphpscript.com?user=username”
3. Launch the query, fetch the output, which is just a PHP echo with just a number as output, i.e. “458” (followers) - actually if you paste the URL in your browser you’d also get “458” since there’s just a PHP echo in the background.
4. Update my ‘followers’ field with the result.

I know how to do 1, 2 and 4, but I have no idea how to do number 3. I’m using the “fetch” method, but I’m getting error, output format errors and plenty of exceptions. It can’t be very long nor difficult, but I’m so stuck. Any help, please?

Thanks!!

[Dominik_Bosnjak]

What are the exact errors? And if this is done inside the Scripting app and not automation, what happens when you use remoteFetchAsync instead of vanilla fetch?

I’m originally a programmer but I’m completely new to Airtable and the script syntax.

Considering how JavaScript is basically PHP for people who hate PHP, I feel for you.

[Bizarrebra]

First I’m just trying to see if it catches a simple “fetch”. Here’s my code below - I’ve copied from an existing script, so it might need some fine-tuning, I guess.

let result = await view.selectRecordsAsync();
for (let record of result.records) {
    let query = record.getCellValueAsString(queryColumn)

    if (record.getCellValue(resultColumn) == null) {
        let response = await remoteFetchAsync('https://myurl'); //modified, but the URL exists
        console.log(await response.json());
        await table.updateRecordAsync(record, {[resultColumn]: response});
    }
}
output.markdown('# done')

I’ve solved the URL error, but I can’t update my field with the result in ‘response’. I’m getting the error below. What is the field type I have to use for ‘Result’? I’ve tried “Single line text” and “Long text” with no success.

j: Can't set cell values: invalid cell value for field 'Result'.
Cell value has invalid format: <root> must be a string
    at spawnErrorWithOriginOmittedFromStackTrace on line 1
    at _assertMutationIsValid on line 1
    at applyMutationAsync$ on line 1
    at tryCatch on line 1
    at invoke on line 1
    at tryCatch on line 1
    at invoke on line 1
    on line 1
    at Promise
    at callInvokeWithMethodAndArg on line 1
    at enqueue on line 1
    on line 1
    at updateRecordsAsync$ on line 1
    at tryCatch on line 1
    at invoke on line 1
    at tryCatch on line 1
    at invoke on line 1
    on line 1
    at Promise
    at callInvokeWithMethodAndArg on line 1
    at enqueue on line 1
    on line 1
    at updateRecordAsync$ on line 1
    at tryCatch on line 1
    at invoke on line 1
    at tryCatch on line 1
    at invoke on line 1
    on line 1
    at Promise
    at callInvokeWithMethodAndArg on line 1
    at enqueue on line 1
    on line 1
    at tu on line 1
    @
    at asyncFunctionResume
    @[native code]
    at promiseReactionJobWithoutPromise
    at promiseReactionJob

[JonathanBowen]

@Bizarrebra - response.json() is going to give you a Javascript object, so this probably isn’t the thing you want to insert into a field on your table.

What I would do first is just console log response.json() and see what you get. You might need to pick out part of this for your field update or turn it into a string or some other manipulation before updating the record.

[kuovonne]

If your response from your PHP echo is just text, try …

let response = await remoteFetchAsync('https://myurl'); //modified, but the URL exists
let responseAsText = await response.text()
console.log(responseAsText )
await table.updateRecordAsync(record, {[resultColumn]: responseAsText});

Note that this is not an Airtable specific issue. Rather this relates to the JavaScript response.

[Bizarrebra]

Wow, you are a star. Thanks!

[Bizarrebra]

So… back here. Thanks a lot for your replies. I’ve got it to work and I can see in the console my JSON back with all the fields returned from my PHP script from the external server. Now, I’m stuck in the easiest part: how to grab those individual values from the JSON? I’ve tried brackets, parenthesis… nothing seems to grab the field names nor values. This is my (almost) final code:

// Looping all records in the table
for (let currentRecord of query.records) {
    let currentRecordName = currentRecord.name.toLowerCase();
    let currentRecordId = currentRecord.id;
    let url = APIURL + currentRecordName;
    let twitchQuery = await remoteFetchAsync(url);
    let twitchStats = twitchQuery.json();

    console.log(await twitchStats); // Shows the JSON block on console perfectly populated
    console.log(await twitchStats['followers']); // Does NOT show the value of the "followers" field
    console.log(await twitchStats['followers'].value); // Returns error

// Update records
//    await TABLE.updateRecordAsync(currentRecordId, {[FIELDTO]: fetchOutput});
}

output.markdown('# done')

[kuovonne]

You are awaiting the wrong thing. Await the conversion to json once, and only once so that the promise resolves. Once the promise resolves, awaiting no longer makes sense.

    let twitchStats = await twitchQuery.json();

    console.log(twitchStats);
    console.log(twitchStats['followers']);
    console.log(twitchStats['followers'].value);

Again, this is a JavaScript issue relating to promises and awaiting asynchronous calls, not an Airtable issue.``

[Bizarrebra]

It’s solved. Thanks.

[kuovonne · Accepted solution]

You need to await the fetch, and await the conversion of the response to json. Once the response has been converted to json, you don’t await it again.

You need to await asynchronous calls, You do not await other calls.