*** Saving original post for context but here is my boiled down question? ***
I have a google sheet as database for an appsheet-app.
To use an apps script in my app it need to be a standalone script (not tied to a google sheet)
But I want the script to uppdate my sheet (aka my database for my app)
How do i solve this?
*** original post here ***
I’m trying to make an app where you enter an tmdb-id and it fetches tv-show-data.
At first I made tables in an appsheet and then a standalone apps script function to get data.
My problem then was that I couldn’t use automation/bot because there is no service for appsheet for me. Then bard advised me to make an appsheet that uses a google sheet as a database instead, because the script for connecting to my tmdb-api worked fine.
So I made a new verison where I started with a google sheet with these sheets:
series (id,name,first_air_date,last_air_date,poster_path,next_episode_to_air,last_episode_to_air)
seasons(id,air_date,episode_count,name,season_number) where id is a ref to series
episodes(id,air_date,episode_number,name,season_number) where season_number is a ref to seasons
And then clicked extensions/AppsSheet/Create an app
But now when i try to make a bot when an series-row is created or updated i get errors that TypeError: Cannot read properties of null (reading ‘getSheetByName’)
at updateSeriesTable(Code:93:33)
at fetchTVShowData(Code:17:1)
…so now I can read from the api,but i cant update my Googlesheets/appSheet data
Where do i get it wrong??
function fetchTVShowData(tvShowId=95396) {
// Get the data from the TMDb API
var apiKey = “MyKey”;
//var encodedApiKey = Base64.encodeByteArray(apiKey);
//var seriesId = activeRange.getValues()[0][0];
var url = [https://api.themoviedb.org/3/tv/](https://api.themoviedb.org/3/tv/)${tvShowId}?api_key=${apiKey};
var response = UrlFetchApp.fetch(url);
var jsonData = JSON.parse(response.getContentText());
// Extract the TV show data
var series = jsonData;
// Fetch seasons and episodes data
//var seasonsData = fetchSeasonsData(series.id);
//var episodesData = fetchEpisodesData(series.id);
// Update Google Sheet tables
updateSeriesTable(series);
//updateSeasonsTable(seasonsData);
//updateEpisodesTable(episodesData);
//return('Klart: '+ tvShowId);
}
function fetchSeasonsData(tvShowId) {
// Get seasons data
var apiKey = “MyKey”;
var url = [https://api.themoviedb.org/3/tv/](https://api.themoviedb.org/3/tv/)${tvShowId}/seasons?api_key=${apiKey};
var response = UrlFetchApp.fetch(url);
var seasonDataArray = JSON.parse(response.getContentText());
var seasonsData = ;
// Parse seasons data
for (var i = 0; i < seasonDataArray.length; i++) {
var seasonData = seasonDataArray[i];
var season = {
season_number: seasonData.season_number,
air_date: seasonData.air_date,
episode_count: seasonData.episode_count,
name: seasonData.name,
tvShowId: tvShowId
};
seasonsData.push(season);
}
return seasonsData;
}
function fetchEpisodesData(tvShowId) {
// Get episodes data
var seasonsData = fetchSeasonsData(tvShowId);
var episodesData = ;
// Recursively fetch episode data for each season
for (var i = 0; i < seasonsData.length; i++) {
var seasonData = seasonsData[i];
var seasonEpisodesData = fetchEpisodesInSeason(seasonData.season_number);
seasonData.episodes = seasonEpisodesData;
episodesData.push(seasonData);
}
return episodesData;
}
function fetchEpisodesInSeason(seasonNumber) {
// Get episodes data for a specific season
var apiKey = “MyKey”;
var url = [https://api.themoviedb.org/3/tv/](https://api.themoviedb.org/3/tv/)${seasonNumber}?api_key=${apiKey};
var response = UrlFetchApp.fetch(url);
var episodeDataArray = JSON.parse(response.getContentText());
var episodeData = ;
// Parse episode data
for (var i = 0; i < episodeDataArray.length; i++) {
var episodeData = episodeDataArray[i];
var episode = {
episode_number: episodeData.episode_number,
air_date: episodeData.air_date,
title: episodeData.name,
season_number: seasonNumber,
tvShowId: episodeData.tv_show_id
};
episodeData.push(episode);
}
return episodeData;
}
function updateSeriesTable(tvShow) {
// Get the active spreadsheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Get the series table
var seriesTable = spreadsheet.getSheetByName(“series”);
// Create a new row
var rowNumber = seriesTable.getLastRow() + 1;
seriesTable.appendRow([tvShow.name, tvShow.id]);
}
function updateSeasonsTable(seasonsData) {
// Get the active spreadsheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Get the seasons table
var seasonsTable = spreadsheet.getSheetByName(“seasons”);
// Iterate through seasons data
for (var i = 0; i < seasonsData.length; i++) {
var seasonData = seasonsData[i];
}
}
