Skip to main content
Media Monitoring & Smart Dispatcher reads all keyword and media catalog data from a single Google Spreadsheet at startup. The spreadsheet is fetched once per import session via the Google Visualization API and parsed in-memory — no Google API key or OAuth credentials are required.

​
How the integration works

The service builds a URL using the spreadsheet ID stored in SharedService, fetches the sheet as a JSON payload, strips a fixed-length wrapper added by the Visualization API, and hands the resulting data to the import component for parsing.

​
Spreadsheet ID

The spreadsheet ID is stored as a field on SharedService:
shared.service.ts
sheetPalabrasClave: string = '1ELi_nCP6XEh4VjB5jTbw8mD7ma2L67qTxPHRwWv6xak';
To point the application at a different spreadsheet, replace this value with your own spreadsheet ID. The ID is the long alphanumeric segment in the spreadsheet’s URL: 
https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit

​
Updating the spreadsheet ID

1

Copy your spreadsheet ID

Open your Google Spreadsheet. From the address bar, copy the segment between /d/ and /edit. For example:
https://docs.google.com/spreadsheets/d/1ELi_nCP6XEh4VjB5jTbw8mD7ma2L67qTxPHRwWv6xak/edit
                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2

Open shared.service.ts

Locate src/app/services/shared.service.ts in the project source. Find the sheetPalabrasClave field.
3

Replace the ID

Replace the existing ID string with your new spreadsheet ID:
shared.service.ts
sheetPalabrasClave: string = 'YOUR_SPREADSHEET_ID_HERE';
4

Rebuild and verify

Restart the Angular dev server (ng serve) and perform a test import. The application fetches the sheet on every import, so no cache clearing is required.

​
Visualization API URL

SharedService.getPalabrasClave() constructs the fetch URL using the stored ID:
shared.service.ts
async getPalabrasClave() {
  const URL = `https://docs.google.com/spreadsheets/d/${this.sheetPalabrasClave}/gviz/tq?tqx=out:json`;
  return this.http.get(URL, { responseType: 'text' });
}
The tqx=out:json query parameter instructs the Visualization API to return data as JSON. The response is typed as text rather than json because the API wraps its payload in a non-standard prefix that must be removed before parsing.

​
Response parsing

The Visualization API response is not raw JSON — it is wrapped in a JavaScript callback stub. The import component strips this wrapper before calling JSON.parse:
importar.component.ts
const jsonData = JSON.parse(data.substring(47).slice(0, -2));
OperationEffect
data.substring(47)Removes the 47-character prefix (e.g. /*O_o*/\ngoogle.visualization.Query.setResponse()
.slice(0, -2)Removes the 2-character suffix ();)
JSON.parse(...)Parses the remaining string as standard JSON
The prefix length of 47 characters is a fixed property of the Google Visualization API response format. If Google changes this format, parsing will break silently. Test after any unexpected import failures by logging the raw data string.

​
Spreadsheet column structure

The application reads two logical datasets from the same sheet. Both datasets share the same rows — each row may contribute to keywords, the media catalog, or both.
ColumnIndexField nameDescription
A0palabraKeyword or phrase used for text matching
B1padreParent category (government area). Use semicolons to assign multiple categories, e.g. Salud;Destacadas
C2(unused)Reserved; not read by the application
D3siglaMedia outlet or program abbreviation (e.g. LV4)
E4nombreFull name of the media outlet or program (e.g. La Voz 4)
The first row is treated as a header. After loading, the application calls .splice(0, 1) on both the keywords array and the media catalog array to discard it.

​
Permissions

The spreadsheet must be accessible without authentication. The Visualization API fetches the sheet as an anonymous HTTP request — there is no OAuth flow in the application.
1

Open sharing settings

In Google Sheets, click Share in the top-right corner.
2

Set access to Anyone with the link

Under General access, select Anyone with the link and set the role to Viewer.
3

Publish to the web (recommended)

For the most reliable access, also go to File → Share → Publish to web, select the target sheet, and click Publish. This ensures the Visualization API endpoint remains accessible even if the sharing settings are later changed.
If the spreadsheet is not publicly accessible, the HTTP request will return an HTML error page instead of JSON. The JSON.parse call will throw, and the import will fail silently. Always verify permissions after creating or duplicating the spreadsheet.

​
Troubleshooting

  1. Confirm the spreadsheet ID in shared.service.ts is correct.
  2. Open the Visualization API URL directly in your browser: 
    https://docs.google.com/spreadsheets/d/<ID>/gviz/tq?tqx=out:json
    If you see an HTML page or a 401 error, the spreadsheet is not publicly accessible. Review the permissions steps above.
The 47-character prefix strip assumes the standard Visualization API wrapper. Log the raw response to verify:
console.log('raw length:', data.length);
console.log('prefix:', data.substring(0, 50));
Adjust the substring offset if Google has changed the wrapper format.
The application removes the first row of each array with .splice(0, 1) after loading. If your spreadsheet does not have a header row in row 1, real data will be discarded. Ensure row 1 contains column labels (e.g. Keyword, Category, (blank), Sigla, Nombre).

Build docs developers (and LLMs) love

Get started for freeTalk to us