Hello,
I have this javascript that i want to add in my automation in order to transpose a table but i always have this error message "Exceed quota 30 table queries". How to solve it?
// Get the "Forecast artikel" table and the "Transposed" table
const tableForecastArtikel = base.getTable("Forecast Artikel");
const tableTransposed = base.getTable("Transposé");
// Retrieve all records from the "Forecast artikel" table
const queryForecastArtikel = await tableForecastArtikel.selectRecordsAsync();
// Array to store updates
let updates = [];
// Iterate through records in the "Forecast artikel" table
for (const recordForecastArtikel of queryForecastArtikel.records) {
// Get the article name from the "Name" column of "Forecast artikel"
const articleName = recordForecastArtikel.getCellValue("Name");
// Iterate through columns like "Tag 1," "Tag 2," "Tag 3," and so on in "Forecast artikel"
for (let i = 1; i <= 31; i++) { // Make sure to adjust the upper limit based on the number of columns in "Forecast artikel"
const columnName = `Tag ${i}`;
const cellValue = recordForecastArtikel.getCellValue(columnName);
if (!isNaN(cellValue)) {
// Retrieve corresponding records in "Transposed" based on "Name" and "articleName"
const queryTransposed = await tableTransposed.selectRecordsAsync({
filterByFormula: `AND({Name} = '${columnName}', FIND('${articleName}', Name) > 0)`
});
// Update corresponding records in "Transposed" with values from "Forecast artikel"
for (const recordTransposed of queryTransposed.records) {
try {
const transposedName = recordTransposed.name;
const transposedNameParts = transposedName.split(' ');
const lastPart = transposedNameParts[transposedNameParts.length - 1];
if (lastPart === i.toString()) {
updates.push({
id: recordTransposed.id,
fields: {
[articleName]: cellValue,
}
});
}
} catch (error) {
console.error(`Error during update: ${error.message}`);
// Continue with other records in case of an error
}
}
}
}
}
// Split the "updates" array into batches of 30 updates and perform the updates
while (updates.length > 0) {
const batchUpdates = updates.slice(0, 30);
updates = updates.slice(30);
// Use the "updateRecordsAsync" function to update records in batches
await tableTransposed.updateRecordsAsync(batchUpdates);
}
console.log("Data update in the 'Transposed' table from 'Forecast artikel' completed successfully.");
Thank you !
Emilie
