With Google Apps Script and a little help from any AI chatbot, we build a small data-collection pipeline in a cloud-based spreadsheet that runs without our intervention.
Collecting data on a topic that produces new information at regular intervals can be a tedious process — but one that can be automated in a few easy steps, without having to resort to specialized or paid software.
This is possible by using Google’s spreadsheets (Google Sheets) and the Google Apps Script platform connected to them — a cloud platform on which we can run JavaScript code. Indicatively, we hereby collect, via an API, and clean data from a Wikipedia table that compiles the highest-grossing films of each week.
Step by step
The first step is to go to the Google Sheets environment. If you have a Google account, any change you make to the spreadsheet will be saved to your Drive.
Inside the spreadsheet environment, at the top of the screen you will find the “Extensions” menu, under which you will find Apps Script.
When you select it, you will be taken to the Google Apps Script environment in a new tab.
This is where you will write the code you need. In this case, working together with Claude, we wrote the following code, which retrieves the data from the page’s “Number-one films” table via the Wikipedia API, writes it to the spreadsheet, and then “cleans” it.
function updateBoxOfficeTable() {
const sheetName = "Box Office";
const pageName = "List_of_2026_box_office_number-one_films_in_the_United_States";
const apiUrl =
"https://en.wikipedia.org/w/api.php?action=parse&page=" +
pageName +
"&prop=text&format=json&origin=*";
const response = UrlFetchApp.fetch(apiUrl);
const json = JSON.parse(response.getContentText());
const html = json.parse.text["*"];
const tables = html.match(
/<table[^>]*class="[^"]*wikitable[^"]*"[^>]*>[\s\S]*?<\/table>/gi
);
if (!tables || tables.length === 0) {
throw new Error("No Wikipedia tables found.");
}
let targetTable = null;
for (let table of tables) {
if (table.includes("Week number")) {
targetTable = table;
break;
}
}
if (!targetTable) {
throw new Error("Could not find the box office table.");
}
const rows = targetTable.match(/<tr[\s\S]*?<\/tr>/gi);
const data = rows.map(row => {
const cells = row.match(
/<(th|td)[^>]*>[\s\S]*?<\/\1>/gi
) || [];
return cells.map(cell => {
return cell
// Remove citations
.replace(/<sup[^>]*>[\s\S]*?<\/sup>/gi, "")
// Remove images
.replace(/<img[^>]*>/gi, "")
// Replace links with text
.replace(/<a[^>]*>(.*?)<\/a>/gi, "$1")
// Remove tags
.replace(/<[^>]+>/g, "")
// Decode HTML entities
.replace(/ /g, " ")
.replace(/&/g, "&")
.replace(/"/g, '"')
.replace(/'/g, "'")
.replace(/</g, "<")
.replace(/>/g, ">")
.trim();
});
});
const cleanedData = data.filter(row => row.length > 0);
const maxColumns = Math.max(
...cleanedData.map(row => row.length)
);
cleanedData.forEach(row => {
while (row.length < maxColumns) {
row.push("");
}
});
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(sheetName);
if (!sheet) {
sheet = ss.insertSheet(sheetName);
}
sheet.clearContents();
const targetRange = sheet.getRange(1, 1, cleanedData.length, maxColumns);
targetRange.setNumberFormat("@STRING@");
targetRange.setValues(cleanedData);
sheet.autoResizeColumns(1, maxColumns);
}
function cleanBoxOfficeTable() {
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName("Box Office");
const range = sheet.getDataRange();
const values = range.getValues();
values[0][0] = "#";
let lastFilm = "";
for (let i = 1; i < values.length; i++) {
// Current columns:
// A = Week
// B = Weekend end date
// C = Film
// D = Gross
// E = Notes
// F = Ref
// Normalize to string before checking, since a cell COULD still be
// a Number/Date object if number formatting is ever reset elsewhere.
const filmCell = values[i][2];
const filmCellStr = filmCell instanceof Date
? Utilities.formatDate(filmCell, Session.getScriptTimeZone(), "M/d/yyyy")
: String(filmCell);
// If column C is actually a dollar amount, shift values left
if (filmCellStr.trim().startsWith("$")) {
values[i][5] = values[i][4];
values[i][4] = values[i][3];
values[i][3] = values[i][2];
values[i][2] = "";
}
if (values[i][2] === "" || values[i][2] === null) {
values[i][2] = lastFilm;
} else {
lastFilm = values[i][2];
}
}
range.setNumberFormat("@STRING@");
range.setValues(values);
sheet.autoResizeColumns(1, 6);
}
This is accomplished with two different functions, which are activated separately within the Apps Script environment. To run the code, we must first have saved it. Then we click “Run.” If this is the first time we are running this particular file, the system will take us through a series of prompts to grant access, which we will need to accept.
After the function has been executed, the data in the spreadsheet will take the following form:
Fire News Alerts Greece: Real-time wildfire updates on Telegram
An automated Telegram channel, Fire News Alerts Greece, sends real-time notifications about new wildfires and de-escalations of active fire fronts.
The data has been added to the sheet, in a new tab named “Box Office.” However, the data contains errors, as it has been placed in the wrong columns. This is where the second function (cleanBoxOfficeTable) of the full code shown above comes in.
To run the next function within Apps Script, we go to the right of the “Debug” option and click on the function name. This activates a dropdown menu from which we select the function we want.
We select the “cleanBoxOfficeTable” function and then click “Run.” We wait for it to finish and then go back to our spreadsheet, where the data will now have been “aligned” into the correct columns.
Automating the execution of the script can be done in two ways: either with code or — more easily — through the options provided by the Apps Script environment. For the second option, we go to the alarm-clock icon on the left side of the page, called “Triggers.” There, we can choose to add a new trigger that will activate a specific function, at specific time intervals, and so on.
Since this particular dataset isn’t updated very often, we chose to run the code once a week, essentially performing the manual work that a person would do by copy-pasting the table.
It should be noted that taking into account permissions/licenses that websites and APIs provide is recommended, when carrying out automated data collection.
