Help

Re: Hitting 30 sec script limit... What to change?

Solved
Jump to Solution
3399 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Kaleb_Penner1
5 - Automation Enthusiast
5 - Automation Enthusiast

As the title says. How would this be written differently to avoid this error? My test environment only had 10 records, tonight was its first scheduled automation run and it grabbed 95 records for this process.

// After Club Automations

let table = base.getTable(“Children”)
let view = table.getView(“Present”);
let query = await view.selectRecordsAsync ({
fields:[“Pts this week”, “Spent Today”, “Total Spent”, “Points”, “Attendance”, “Last Club”],
sorts: [
{field: “Pts this week”},
]
});

let totes = 0;
let today = new Date()
for (let record of query.records){

// Multiply 'Pts this week" by 10, add to ‘Points’, Subtract 'Spent Today, write new total to “Points” field

totes = 10 * Number(record.getCellValueAsString("Pts this week"));
totes = totes + record.getCellValue("Points") - record.getCellValue("Spent Today");
await table.updateRecordAsync(record, {"Points": totes});

// Add ‘Spent today’ to ‘Total Spent’ and clear ‘Spent today value’

totes = record.getCellValue("Spent Today") + record.getCellValue("Total Spent");
await table.updateRecordAsync(record, {"Total Spent": totes});
await table.updateRecordAsync(record, {"Spent Today": null});

// Add one to Attendance Column

totes = record.getCellValue("Attendance") +1
await table.updateRecordAsync(record, {"Attendance": totes});

// Update Last Club with Today’s Date

await  table.updateRecordAsync(record, {"Last Club": today});

// Clear Pts this week field

await table.updateRecordAsync(record, {"Pts this week": null});

}

Thanks in Advance!

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

Your problem is that your script is updating 95 records 6 times each, one at a time. That’s 570 updates, which could be just 2:

  • Any time you need to update/create/delete more than one record you should be using the batch version of the appropriate function. In your case that would be updateRecordsAsync.
  • There appears to be no reason for your script to update the same record 6 times, once per effected field, instead of passing all fields at once.
  • The maximum batch amount Airtable allows is 50 requests. Therefore since you had 95 records, you can accomplish your updates with 2 batches (one with 50 and another with the remaining 45).
  • There are a great many number of examples of scripts on these forums, or in the scripting API’s documentation.

Essentially, you’d replace the entirety of your for loop with something like:

let updates = query.records.map(record => {
   return {
      id: record.id,
      fields: {
         "Points": (10 * Number(record.getCellValueAsString("Pts this week"))) + record.getCellValue("Points") - record.getCellValue("Spent Today"),
         "Total Spent": record.getCellValue("Spent Today") + record.getCellValue("Total Spent"),
         "Spent Today": null,
         "Attendance": record.getCellValue("Attendance") +1,
         "Last Club": today,
         "Pts this week": null
      }
   }
})

while (updates.length > 0) {
   await table.updateRecordsAsync(updates.slice(0, 50))
   updates = updates.slice(50)
}

See Solution in Thread

4 Replies 4
Kamille_Parks
16 - Uranus
16 - Uranus

Your problem is that your script is updating 95 records 6 times each, one at a time. That’s 570 updates, which could be just 2:

  • Any time you need to update/create/delete more than one record you should be using the batch version of the appropriate function. In your case that would be updateRecordsAsync.
  • There appears to be no reason for your script to update the same record 6 times, once per effected field, instead of passing all fields at once.
  • The maximum batch amount Airtable allows is 50 requests. Therefore since you had 95 records, you can accomplish your updates with 2 batches (one with 50 and another with the remaining 45).
  • There are a great many number of examples of scripts on these forums, or in the scripting API’s documentation.

Essentially, you’d replace the entirety of your for loop with something like:

let updates = query.records.map(record => {
   return {
      id: record.id,
      fields: {
         "Points": (10 * Number(record.getCellValueAsString("Pts this week"))) + record.getCellValue("Points") - record.getCellValue("Spent Today"),
         "Total Spent": record.getCellValue("Spent Today") + record.getCellValue("Total Spent"),
         "Spent Today": null,
         "Attendance": record.getCellValue("Attendance") +1,
         "Last Club": today,
         "Pts this week": null
      }
   }
})

while (updates.length > 0) {
   await table.updateRecordsAsync(updates.slice(0, 50))
   updates = updates.slice(50)
}

Coming back to this after a while.

Reading up on the documentation a bit and looking at your response has been helpful.

Something I am not able to resolve however is that I get the following error when I try to run it:

ERROR

TypeError: Invalid arguments passed to table.updateRecordsAsync(records): • records[0].id should be a string, not an object.

I am working on addressing it, I think I understand the error correctly but not sure of the fix just yet. I’ll post and maybe will figure it out before I get a response.

Here is the whole code now:

 let table = base.getTable("Children")

 let view = table.getView("Present");

 let query = await view.selectRecordsAsync ({

     fields:["Pts this week", "Spent Today", "Total Spent", "Points", "Attendance", "Last Club"],

     sorts: [

         {field: "Pts this week"},



]

 });

 let totes = 0;

 let today = new Date()

 let updates = query.records.map(record => {
    return {
       id: record,
  fields: {
     "Points": (10 * Number(record.getCellValueAsString("Pts this week"))) + record.getCellValue("Points") - record.getCellValue("Spent Today"),
     "Total Spent": record.getCellValue("Spent Today") + record.getCellValue("Total Spent"),
     "Spent Today": null,
     "Attendance": record.getCellValue("Attendance") +1,
     "Last Club": today,
     "Pts this week": null
   }
 }
 })

 while (updates.length > 0) {
   await table.updateRecordsAsync(updates.slice(0, 50))
  updates = updates.slice(50)
 }

change

id: record

to

id: record.id

Wowzers. Yes that will do it. Thank you very much for the help and opportunity to learn!