I keep getting this error "Script function not found: generateDocument("Mickeys Bakery", "Tennis")" However the function works and this is how it is defined:
function generateDocument(sponsorName, programName)
The function above is bing called in custom button the button has a dropdown with sponsor names, under those names are programme names so when I click on Tennis which is under Mickeys Bakery the button should run the function with variables above. this is what the code looks like:
var SPONSORS = []; // Initialize empty sponsors array
const SPREADSHEET_ID = 'sheet_id';
const TEMPLATE_DOC_ID = 'doc_id';
const flaggedStudentsSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName('Students');
const sponsorsSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName('Sponsors');
// Function to fetch the list of sponsors and their sponsored programs
function fetchSponsorsList() {
try {
var sponsorsSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName('Sponsors');
// Get data from the Sponsors sheet
var dataRange = sponsorsSheet.getDataRange();
var values = dataRange.getValues();
// Initialize an empty array to hold sponsors and their programmes
var sponsorsData = [];
// Loop through the data to fetch sponsors and their programmes
for (var i = 1; i < values.length; i++) {
var sponsor = values[i][1]; // Assuming sponsor name is in the second column
var sponsoredProgrammes = values[i][2].split(", "); // Assuming sponsored programmes are in the third column
// Push an object containing sponsor name and their sponsored programmes to the array
sponsorsData.push({ sponsor: sponsor, sponsoredProgrammes: sponsoredProgrammes });
}
// Update the SPONSORS list with the fetched data
SPONSORS = sponsorsData;
Logger.log("Sponsors list and sponsored programmes fetched successfully.");
} catch (error) {
Logger.log("Error fetching sponsors list and sponsored programmes: " + error.toString());
}
}
// Function to generate a document based on the selected sponsor and program
function generateDocument(sponsorName, programName) {
try {
// Get data from the first row of Flagged Students sheet
var data = flaggedStudentsSheet.getRange(2, 1, 1, flaggedStudentsSheet.getLastColumn()).getValues()[0];
// Sponsor Details
body.replaceText('{{s}}', sponsorName); // Sponsor name
body.replaceText('{{p}}', programName); // Programme name
// Student Details
body.replaceText('{{name}}', data[1]); // Student Name
body.replaceText('{{team_coach}}', data[2]);
body.replaceText('{{sport}', data[18]);
body.replaceText('{{team_performance}}', data[15])
body.replaceText('{{attendance}}', data[14]);
Logger.log('Document created successfully.');
} catch (e) {
Logger.log('Error: ' + e.toString());
}
}
// Function to create a sponsor selection menu in the UI
function sponsorButton() {
const ui = DocumentApp.getUi();
const sponsorsMenu = ui.createMenu('Select A Sponsor');
// Add submenus for each sponsor
SPONSORS.forEach(function(sponsorData) {
const sponsor = sponsorData.sponsor;
const sponsoredProgrammes = sponsorData.sponsoredProgrammes;
const sponsorSubMenu = ui.createMenu(sponsor);
// Add submenu items for each sponsored programme
sponsoredProgrammes.forEach(function(programme) {
sponsorSubMenu.addItem(programme, 'generateDocument("' + sponsor + '", "' + programme + '")');
});
sponsorsMenu.addSubMenu(sponsorSubMenu);
});
// Add the sponsor menu to the UI
sponsorsMenu.addToUi();
}
function onOpen() {
fetchSponsorsList(); // Fetch sponsors list and
sponsorButton(); // Dropdown button
}
Few things that are clear, the function function generateDocument(sponsorName, programName) exist and when I run it directly from the App Script editor with static variables it works just fine, and the button function seems to work when I switch for a different function that does not require parameters but when it tries to call the function in question only then do I get the error above. I do not believe that the issue is with the variables as you can see from the error the function seems to have pulled correct data from the dropdown for both variables "Mickeys Bakery" and "Tennis"
The script is attached to a google doc, the template_doc_id above belongs to this doc, the doc get's data from a google sheet represented by the spreadsheet_id above as well and a pic below is a sample of what the data looks for one of the sheets. as for the dropdown button it contains sponsor names in the menu with corresponding programme names making up the submenu for each sponsor the button reside on the document and when clicked it is supposed to fill all the place holders "{{s}}" with details from the spread sheet(please check the image linked below). When trying to reproduce this please ignore the student details part and only pay attention to the sponsor details part that's the important one.