Automated Table Preparation for Looker Studio Reports

The project is implemented as a Google Apps Script to prepare working data from Google Sheets for further visualization in Looker Studio. The code works with two datasets: the `FBO` sheet with inventory balances and the `search_queries` sheet with search queries. The central function `updateAllLookerTables()` serves as a single entry point and sequentially calls two operations: rebuilding the FBO table and copying the search queries table.

Reshaping FBO to long format

The main technical block of the project is the function `buildFboLongTable()`. It opens the source Google Sheet by URL, retrieves the `FBO` sheet, reads the entire data range using `getDataRange().getValues()`, and transforms the original wide data structure into a long tabular format. In the original table, products are stored in a single row, while warehouses are represented as separate columns. The script expands these warehouse columns into separate rows, where each row contains the basic product data, the specific warehouse, the balance at that warehouse, and the geographical grouping for Looker Studio.

Geo enrichment via mapping

To enrich the data, a separate `mapping` sheet in the active table is used. An object `geoMap` is built from it, where the key is the warehouse name and the value is the `Geo for Looker` field. When processing FBO balances, the script matches each warehouse with a geographical group and adds this value to the final table. If a warehouse is not present in the mapping table, the geo field remains empty. This approach separates the mapping reference from the main processing logic and allows changing the warehouse-to-geo mapping without modifying the code.

Batch result writing

Before writing the result to the target `FBO` sheet in the active table, it is cleared using `clearContents()`. After that, the script forms a new data array in memory: it first adds headers, then processes products and warehouses row by row. The final table is written in a single `setValues()` operation, which reduces the number of calls to the Google Sheets API compared to row-by-row writing and makes execution faster and more stable over large ranges.

Copying search queries

The second part of the project is the function `buildSearchQueries2Table()`. It opens the same data source, reads the `search_queries` sheet, and completely copies its contents to the `search_queries_2` sheet in the active table. If the target sheet is missing, it is automatically created using `insertSheet()`. Before writing, old data is cleared, after which the entire array is transferred in a single `setValues()` operation.

Sheet and data validation

The code includes basic checks for the presence of sheets and input data. If the source `FBO` sheet, target `FBO` sheet, `mapping` sheet, or `search_queries` sheet is not found, the script throws a clear error with the name of the problematic sheet. For `search_queries`, it is also checked that the source sheet is not empty. The project has no external npm dependencies, databases, user interfaces, build tools, or separate backend layers; all logic is built on the built-in capabilities of Google Apps Script and the Google Sheets API.