Automatically Changing a YouTube Video's Privacy With Google Apps Script

 Recently we had posted a video that needed to be available during a specific time. The starting availability is taken care of by setting it to premiere. However there's no option for having it unavailable at the end of the time period, so we needed to write some code for that.

Google Apps Script is a good choice for this, since it has a YouTube service that you can enable as well as triggers that can run code at a certain time.

Create a new Apps Script project and paste in the following code:

Then enable the YouTube Service and create a new trigger to run the updateVideoPrivacy function at a time-driven specific date and time. The code will then set your most recently uploaded YouTube video to private at the time you specified.

Coding Challenge 3

The third coding challenge is up at misterhay.github.io/coding-challenges, along with a possible solution to this challenge and last month's challenge. 

Small Coding Challenge 2

The second coding challenge is up at misterhay.github.io/coding-challenges, along with a possible solution to this challenge and last month's challenge.

Fixing a formula in multiple Google Sheets

Recently we realized that there was a calculation error in a Google Sheet template, and unfortunately we had already made a copy of it for each teacher.

Rather than opening each Sheet and manually making the correction, I wrote a Google Apps Script to automatically make the replacements. Here is the code in case it is helpful for anyone else:

function loopThem() {
  var folderId = 'aaaaaaaaa'; // the folder containing all of the sheets to be corrected

  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    var id = file.getId();
    fixIt(id);
  }
}

function fixIt(id) {
  var rangeToFix = 'P1:P50';  // the range in each sheet that contains the error
  var replaceThis = '$A';
  var replaceWithThis = '$C';

  var ss = SpreadsheetApp.openById(id);
  var range = ss.getRange(rangeToFix);
  var formulas = range.getFormulas();
  for (var i=0; i<formulas.length; i++) {
    var formula = formulas[i][0];
    var newFormula = formula.replaceAll(replaceThis, replaceWithThis);
    formulas[i][0] = newFormula;
  }
  range.setFormulas(formulas);
}

Small Coding Challenge 1

This year I'm going to design some coding challenges, and hopefully release them at least once a month.

The first coding challenge is up at misterhay.github.io/coding-challenges.

Quick Exploratory Data Analysis with SweetViz

I recently came across a Python library that is useful for quick exploration of a dataset (or two) in a Jupyter notebook, SweetViz.

Code:

try:
    import sweetviz
except:
    !pip install sweetviz --user
    import sweetviz
import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/callysto/hackathon/f7454f5d9234df2575ceb7b8983340e512fad24d/SustainabilityOnMars/Tutorials/pets_from_bootstrap_world.csv')
analysis = sweetviz.analyze(df)
analysis.show_notebook() # or export with show_html()

Here is a brief notebook demonstration.

Getting Student Submission Data from Brightspace with Python and Selenium

As a teacher with students in multiple Brightspace courses, I was looking for a dashboard to show which students have unsubmitted assignments. While Brightspace does have an API available, I decided that it wasn't going to work for a few reasons. There are also commercial (non-free) plugins that can do most of what I was looking for, but this was a good opportunity to explore scraping of content from dynamic web pages with Python.

You may be familiar with the Python Requests and Beautiful Soup libraries, which are great, but since Brightspace is requiring a Microsoft login I needed to go with Selenium. Selenium is designed for automating web browser interactions, which means we can use it to log in to a site and scrape pages.

While Selenium can be installed and run locally, it also works in a Colab notebook:

!apt update
!apt install chromium-chromedriver
!pip install selenium
from selenium import webdriver
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
options = webdriver.ChromeOptions()
options.add_argument('--headless')
options.add_argument('--no-sandbox')
browser = webdriver.Chrome(options=options)

Once that is set up, we need to log in to our Brightspace server:

email = 'teacher@example.com'
base_url = 'https://example.brightspace.com'
import getpass # so you don't show your password in the sourcecode
password = getpass.getpass()
email_field = (By.ID, 'i0116')
password_field = (By.ID, 'i0118')
next_button = (By.ID, 'idSIButton9')
browser.get(base_url)
WebDriverWait(browser,10).until(EC.element_to_be_clickable(email_field)).send_keys(email)
WebDriverWait(browser,10).until(EC.element_to_be_clickable(next_button)).click()
WebDriverWait(browser,10).until(EC.element_to_be_clickable(password_field)).send_keys(password)
WebDriverWait(browser,10).until(EC.element_to_be_clickable(next_button)).click()
WebDriverWait(browser,10).until(EC.element_to_be_clickable(next_button)).click()

From there it's a matter of scraping the course progress pages as we loop through the course IDs and student IDs. I may update this post later with an automated way to scrape these IDs, but for now we need to look them up on Brightspace and code them in:

import pandas as pd
df = pd.Series(students).to_frame('ID')
for course in courses:
    course_id = courses[course]
    submissions = []
    for student in students:
        student_id = students[student]
        url = base_url+'/d2l/le/classlist/userprogress/'+str(student_id)+'/'+str(course_id)+'/Assignments/Details'
        browser.get(url)
        WebDriverWait(browser, 10).until(EC.presence_of_element_located((By.CSS_SELECTOR, "span[class^='d2l-textblock']")))
        elements = browser.find_elements_by_css_selector("span[class^='d2l-textblock']")
        #elements = browser.find_element(by=)  # because the other thing is deprecated I guess
        
        #submitted = elements[13].text[1:-1] # to get a fraction
        submitted = elements[13].text[1:-1].split('/')[0] # to get just the numerator
        submissions.append(submitted)
    df[course] = submissions
df.to_csv('student-submissions.csv')
df

This will give us a Pandas DataFrame (and a CSV file) with information about how many assignments each student has submitted in each course. We can manipulate this a bit to create some visualizations, but perhaps that's a post for later. For now, here's part of a chart that we could generate:

Let me know if you try this or if you come across anything that should be corrected.