Embedded Files
Science Notebook
Science Notebook
Learn how to use Gemini or ChatGPT to develop a Google Apps script based system to create a science notebook for each student, then be able to update each student notebook with new data, appended to the bottem of each document.
We will build this from scratch using a prompt that I already created to submit to the AI to build the apps scripts. Then we will paste these scripts into the Apps Script Ecitor, authorize them to run and troubleshoot the results to build the working system.
At the completion we hope to have a working system that you can use to distribute assignments to your students.
Notebook Template
Notebook Template
Sheet Template
Sheet Template
AI Prompt
AI Prompt
Procedure
Procedure
Create a new folder in your google drive and name it 'Student Notebook Project'.
Make copies of the three files listed above.
When you have the new files, move them into the new folder you just made.
We need to get the URL's of the new Folder, and also of the copy of the Notebook Template that you just made and paste the URL's into the seteup tab of your student notebook master sheet.
Next we should put in just our first and last name on the 'main' tab of the sheet.
Now we will open the AI prompt sheet and we will need to copy the prompt and we will paste it into Gemini to have our scripts generated.
Next we will open the Apps Script Editor which is found under the Extensions menu on the sheet.
When the apps script editor is open, we will rename the script from untitled, let's call it 'student notebooks'.
Then we will highlight all the default text that is in the apps script window and replact it all with the scripts that gemini created.
We will save and run.
The first time we run the scripts it should run the 'onOpen' script. this will bring up the window to authorize all the scripts. If it runs successfully, it should create a new menu on the sheet named 'Student Notebook'.
We will run the first script and see if it successfully creates a new notebook from the template with your name, and place it in your new folder.
If there are errors we will troubleshoot
We will run the other scripts and see if they are working.
After troubleshooting the scripts we should have a working version of this project.
This is the summary of the errors we had to resolve initially to get the scripts to work correctly:
This is the summary of the errors we had to resolve initially to get the scripts to work correctly:
Absolutely. Here's a summary of the errors we encountered and the corrections made:
1. TypeError: templateDoc.makeCopy is not a function:
Error: The original script attempted to use makeCopy() on a DocumentApp.Document object, which doesn't have that method. makeCopy() is a method of the DriveApp.File object.
Correction: We switched to using DriveApp.getFileById() to get the template document as a File object and then used templateFile.makeCopy() to create the new document. We then used DocumentApp.openById() to open the new file as a document.
2. Table Data Not Appending in appendAssignments():
Error: Initially, we tried appending the table data using body.appendTable(assignmentData), which didn't consistently work.
Correction: We switched to a cell-by-cell approach. First, we created an empty table using body.appendTable(). Then, we used nested forEach loops to iterate through the assignmentData and append each cell's value to the table using tableRow.appendTableCell(cellData).
3. Date Objects Not Displaying Correctly in Table:
Error: When date objects from the 'Assignment Template' sheet were directly appended to the table cells, they weren't displayed as formatted dates in the Google Docs.
Correction: Before appending the table data, we used assignmentData.map() to iterate through the array and check if any cell was a Date object. If it was, we formatted it into a string using Utilities.formatDate() before appending it to the table cell.
In essence, the corrections involved:
Using the correct Google Apps Script APIs (DriveApp vs DocumentApp) for file manipulation.
Implementing a robust cell-by-cell approach for table data appending.
Explicitly formatting date objects into strings to ensure correct display in Google Docs.
These corrections highlight the importance of understanding the specific methods available in the Google Apps Script APIs and handling data type conversions carefully.
Finished AppsScript Code
Finished AppsScript Code
const ss = SpreadsheetApp.getActiveSpreadsheet();
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Student Documents')
.addItem('Create Student Documents', 'createStudentDocuments')
.addItem('Append Assignments', 'appendAssignments')
.addItem('Backup and Erase Assignments', 'backupAndEraseAssignments')
.addToUi();
}
function createStudentDocuments() {
const mainSheet = ss.getSheetByName('main');
const setupSheet = ss.getSheetByName('Setup');
const templateId = setupSheet.getRange('A3').getValue();
const folderId = setupSheet.getRange('B3').getValue();
const folder = DriveApp.getFolderById(folderId);
const data = mainSheet.getDataRange().getValues();
data.forEach((row, index) => {
if (index === 0) return; // Skip header row
if (row[3]) return; // Skip rows with existing document URLs
const firstName = row[0];
const lastName = row[1];
const email = row[2];
const templateFile = DriveApp.getFileById(templateId); // Get the file using DriveApp
const newFile = templateFile.makeCopy(`${firstName} ${lastName} Notebook`, folder); // Make a copy using DriveApp
const newDoc = DocumentApp.openById(newFile.getId()); // open the file as a document.
const newDocUrl = newDoc.getUrl();
newDoc.addEditor(email);
mainSheet.getRange(index + 1, 4).setValue(newDocUrl); // Write URL to column D
});
}
function appendAssignments() {
const mainSheet = ss.getSheetByName('main');
const assignmentTemplateSheet = ss.getSheetByName('Assignment Template');
let assignmentData = assignmentTemplateSheet.getRange('A1:C10').getValues();
const data = mainSheet.getDataRange().getValues();
const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MM/dd/yyyy');
// Format date objects in assignmentData to strings
assignmentData = assignmentData.map(row => {
return row.map(cell => {
if (cell instanceof Date) {
return Utilities.formatDate(cell, Session.getScriptTimeZone(), 'MM/dd/yyyy');
}
return cell;
});
});
data.forEach((row, index) => {
if (index === 0) return; // Skip header row
const docUrl = row[3];
if (!docUrl) return; //skip if no doc url.
try {
const doc = DocumentApp.openByUrl(docUrl);
const body = doc.getBody();
body.appendParagraph(today).setHeading(DocumentApp.ParagraphHeading.HEADING1);
const table = body.appendTable(); // Create an empty table
assignmentData.forEach(rowData => {
const tableRow = table.appendTableRow();
rowData.forEach(cellData => {
tableRow.appendTableCell(cellData);
});
});
} catch (e) {
Logger.log(`Error processing document ${docUrl}: ${e}`);
}
});
}
function backupAndEraseAssignments() {
const assignmentTemplateSheet = ss.getSheetByName('Assignment Template');
const sheets = ss.getSheets();
let maxAssignmentNumber = 0;
sheets.forEach(sheet => {
const match = sheet.getName().match(/Assignment (\d+)/);
if (match) {
const assignmentNumber = parseInt(match[1]);
maxAssignmentNumber = Math.max(maxAssignmentNumber, assignmentNumber);
}
});
const newSheetName = `Assignment ${maxAssignmentNumber + 1}`;
assignmentTemplateSheet.copyTo(ss).setName(newSheetName);
assignmentTemplateSheet.getRange('A2:C10').clearContent();
}
Report abuse