Using apps script in Google Sheet I can create a new assignment or 'materials' for students in a Google Classroom, passing the information programmatically from Google Sheet.
One of the fields in the Google Sheet - responses from a Google Form - is a link to a file uploaded as evidence. I want to attach that linked file to the 'materials' assignment in Google Classroom.
I have a function that passes all the other information to Google Classroom and I'm sure the file itself is being found and passed to Google Drive but my code is not attaching the file to the 'Materials' assignment. The problem seems to be here -
Classroom.Courses.CourseWorkMaterials.create({
assigneeMode: "INDIVIDUAL_STUDENTS",
individualStudentsOptions: { studentIds: [studentId] },
title: pupil + " - " + curriculumArea,
description: materials.description,
materials: materials.materials
}, courseId);
The error message is
Error
GoogleJsonResponseException: API call to classroom.courses.courseWorkMaterials.create failed with error: Invalid JSON payload received. Unknown name "id" at 'course_work_material.materials[0].drive_file': Cannot find field.
Invalid JSON payload received. Unknown name "title" at 'course_work_material.materials[0].drive_file': Cannot find field.
Invalid JSON payload received. Unknown name "driveFileId" at 'course_work_material.materials[1].drive_file': Cannot find field.
Invalid JSON payload received. Unknown name "title" at 'course_work_material.materials[1].drive_file': Cannot find field.
createMaterialsFromGoogleSheet
Which I think refers to
// Define the materials array
var materials = {
title: pupil + " - " + curriculumArea,
description: "Learning intentions: " + learningIntentions + "\n\nSuccess criteria: " + successCriteria + "\n\nAssessment comments: " + assessmentComments + "\n\nNext steps: " + nextSteps,
materials: [
{
driveFile: {
id: evidenceId,
title: fileName
}
}
]
};
and
// Add the evidence file to the materials
materials.materials.push({
driveFile: {
id: evidenceId,
title: fileName
}
});
This is how these snippets fit into the function
// Loop through each row of data and create a new material for each student
for (var i = 1; i < data.length; i++) {
var pupil = data[i][1];
var curriculumArea = data[i][2];
var learningIntentions = data[i][3];
var successCriteria = data[i][5];
var assessmentComments = data[i][6];
var nextSteps = data[i][7];
var evidenceLink = data[i][8];
// Define the materials array
var materials = {
title: pupil + " - " + curriculumArea,
description: "Learning intentions: " + learningIntentions + "\n\nSuccess criteria: " + successCriteria + "\n\nAssessment comments: " + assessmentComments + "\n\nNext steps: " + nextSteps,
materials: [
{
driveFile: {
id: evidenceId,
title: fileName
}
}
]
};
var evidenceId = "";
var fileName = "";
if (evidenceLink) {
evidenceId = uploadFileToDrive(evidenceLink);
if (!evidenceId) {
Logger.log("Could not upload file: " + evidenceLink);
continue;
}
// Get the file name from the evidenceLink
var fileName = evidenceLink.split("/").pop();
// Add the evidence file to the materials
materials.materials.push({
driveFile: {
id: evidenceId,
title: fileName
}
});
// Add the evidence link to the description
assessmentComments += "\n\nEvidence: " + evidenceLink;
}
if (pupil) {
var studentId = getStudentId(pupil, courseId);
if (!studentId) {
Logger.log("Could not find student with name '" + pupil + "' in course. Skipping row.");
continue;
}
Classroom.Courses.CourseWorkMaterials.create({
assigneeMode: "INDIVIDUAL_STUDENTS",
individualStudentsOptions: { studentIds: [studentId] },
title: pupil + " - " + curriculumArea,
description: materials.description,
materials: materials.materials
}, courseId);
}
}
}
Looks like the error is to do with the drive file. In the google sheet it looks like this - https://drive.google.com/open?id=18xxMxkYp5W5x3NbbMAqVnJOx6pwlxxxx
The code that is called by evidenceId = uploadFileToDrive(evidenceLink); is
function uploadFileToDrive(url) {
var response = UrlFetchApp.fetch(url, {
headers: {
Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
}
});
var file = Drive.Files.insert({
title: "evidence",
mimeType: response.getHeaders()['Content-Type']
}, response.getBlob());
return file.id;
}
In your script, in the following part, evidenceId and fileName are not declared. I think that this is the reason for 1st Unknown name "title" at .
var materials = {
title: pupil + " - " + curriculumArea,
description: "Learning intentions: " + learningIntentions + "\n\nSuccess criteria: " + successCriteria + "\n\nAssessment comments: " + assessmentComments + "\n\nNext steps: " + nextSteps,
materials: [
{
driveFile: {
id: evidenceId,
title: fileName
}
}
]
}
It seems that {driveFile: {id: evidenceId, title: fileName}} should be {driveFile: {driveFile: {id: evidenceId, title: fileName}}}. I think that this is the reason for the 2nd Unknown name "title" at .
When these points are reflected in your script, it becomes as follows.
for (var i = 1; i < data.length; i++) {
var pupil = data[i][1];
var curriculumArea = data[i][2];
var learningIntentions = data[i][3];
var successCriteria = data[i][5];
var assessmentComments = data[i][6];
var nextSteps = data[i][7];
var evidenceLink = data[i][8];
// Define the materials array
var materials = {
title: pupil + " - " + curriculumArea,
description: "Learning intentions: " + learningIntentions + "\n\nSuccess criteria: " + successCriteria + "\n\nAssessment comments: " + assessmentComments + "\n\nNext steps: " + nextSteps,
materials: []
};
var evidenceId = "";
var fileName = "";
if (evidenceLink) {
evidenceId = uploadFileToDrive(evidenceLink);
if (!evidenceId) {
Logger.log("Could not upload file: " + evidenceLink);
continue;
}
// Get the file name from the evidenceLink
var fileName = evidenceLink.split("/").pop();
// Add the evidence file to the materials
materials.materials.push({
driveFile: {
driveFile: {
id: evidenceId,
title: fileName
}
}
});
// Add the evidence link to the description
assessmentComments += "\n\nEvidence: " + evidenceLink;
}
if (pupil) {
var studentId = = getStudentId(pupil, courseId);
if (!studentId) {
Logger.log("Could not find student with name '" + pupil + "' in course. Skipping row.");
continue;
}
Classroom.Courses.CourseWorkMaterials.create({
assigneeMode: "INDIVIDUAL_STUDENTS",
individualStudentsOptions: { studentIds: [studentId] },
title: pupil + " - " + curriculumArea,
description: materials.description,
materials: materials.materials
}, courseId);
}
}