Update Table via API Import

[Gahl_Shalev1]

Hi,

I’m trying to import a dataset of permits in the city of Austin, TX. My goal will be to update this daily, so I can filter by permits add on a given day.

Every time I hit “Run” on my script, it only adds about 1,000-1,500 records to the table (different every time) and it seems to be skipping records, the length of the data set is roughly 20,000 records.

Thanks in advance for any help!

Here’s where I’m importing from:

https://dev.socrata.com/foundry/data.austintexas.gov/mavg-96ck

And here’s my script:

// url to be queried
let url = "https://data.austintexas.gov/resource/mavg-96ck.json?$limit=50000&$$app_token=[MY API TOKEN]";

// set the table that stores the records
let table = base.getTable("Table 1");

// make the API call
let response = await fetch(url, {
    method: "GET"
})

// get the API response
let data = await response.json();
output.text("Retrieved " + data.length + " Records");

data.forEach(async element => {
let newRecord = await table.createRecordAsync({
        "Permit Number": element.permit_number,
        "Case Type": element.case_type,
        "Status": element.status,
        "Application Date": element.application_start_date,
        "Link": element.link,
        "Address": element.street_number + " " + element.street_name +" "+ element.street_type +", "+ element.city +", TX "+ element.zip_code,
        "Owner Name": element.owner_fullname,
        "Owner Company": element.owner_organization_name
    });
});

[Mike_Pennisi]

The problem seems to be that the script starts a bunch of asynchronous operations, but it doesn’t wait for them to complete. This change should help:

-data.forEach(async element => {
+for (const element of data) {
 let newRecord = await table.createRecordAsync({
         "Permit Number": element.permit_number,
         "Case Type": element.case_type,
         "Status": element.status,
         "Application Date": element.application_start_date,
         "Link": element.link,
         "Address": element.street_number + " " + element.street_name +" "+ element.street_type +", "+ element.city +", TX "+ element.zip_code,
         "Owner Name": element.owner_fullname,
         "Owner Company": element.owner_organization_name
     });
-});
+}

That might be surprising because the original version of the code uses the await operator for all the asynchronous function calls. The code

[record1, record2].forEach(async (record) => {
  await table.createRecordAsync(record);
});

Is just like this:

const create = async (record) => {
  await table.createRecordAsync(record);
};
[record1, record2].forEach(create);

And that can be rewritten as

const create = async (record) => {
  await table.createRecordAsync(record);
};
create(record1);
create(record2);

We’re using await inside the function named create, but that function is still asynchronous (it returns a Promise that resolves when the asynchronous operation completes). Because of that, we also need to use await at the “top level” of the script. Otherwise, the scripting block won’t be aware that we’ve started any asynchronous operations at all. It will execute create once (for the first invocation), and then immediate execute it again (for the second invocation), and then reach the end of the program (cutting off the asynchronous operations before they finish).

For this short example, the most direct solution would be to apply the await keyword to each invocation of create:

 const create = async (record) => {
   await table.createRecordAsync(record);
 };
-create(record1);
+await create(record1);
-create(record2);
+await create(record2);

…but this isn’t an option in the real script because we don’t know how many records we’re dealing with. The records are in an array, so if we use a for loop, we can use await on each one:

 const create = async (record) => {
   await table.createRecordAsync(record);
 };
-await create(record1);
-await create(record2);
+for (const record of [record1, record2]) {
+  await create(record);
+}

And now, the create function is superfluous.

-const create = async (record) => {
-  await table.createRecordAsync(record);
-};
 for (const record of [record1, record2]) {
-  await create(record);
+  await table.createRecordAsync(record);
 }

(Alternatively, you could change your code to use Array.prototype.map instead of forEach and pass the result to Promise.all. That’s more of a “functional” style of coding, but the result would be the same as using a for loop.)