A Google Apps Script bound to a Google Sheet that automates the creation of student profile documents. It converts student BMP photo files to JPG format, uploads them to Google Drive, and inserts the image hyperlinks into the spreadsheet for use with a mail-merge tool such as AutoCrat.
Originally created for Northside ISD. Generalized for public use.
When a user clicks Get Picture Hyperlinks → Import Picture Hyperlinks from the custom menu:
- Reads student names and entry dates from the active sheet (columns B and D).
- Looks up each student's BMP photo file in a source Google Drive folder.
- Converts the BMP to JPG by renaming the file blob.
- Uploads the JPG to a destination Google Drive folder.
- Writes the image URL back into column L of the spreadsheet.
- Displays a summary of processed and skipped students.
Rows that already have an image link in column L are skipped automatically, so the script is safe to run multiple times.
- A Google Account with access to Google Sheets and Google Drive
- AutoCrat (for the mail-merge step that generates the profile documents)
- Student BMP photo files uploaded to a Google Drive folder
- The script bound to a Google Sheet with the following structure:
- Setup sheet — contains the Drive folder IDs in cells B3 and B4
- Enrolled sheet — contains student data with names in column B, entry dates in column D, and image links in column L
git clone https://github.com/ILTConsultingLLC/excel-student-profiles-project.gitOr use clasp to push directly to an existing Apps Script project:
npm install -g @google/clasp
clasp login
clasp push- BMP folder — upload student
.bmpphoto files here - JPG folder — converted images will be saved here
- Profiles folder — where AutoCrat will place the finished profile documents
Open the bound Google Sheet, go to the Setup tab, and enter:
| Cell | Value |
|---|---|
| B3 | Google Drive folder ID for BMP source images |
| B4 | Google Drive folder ID for JPG destination images |
To find a folder ID, open the folder in Google Drive and copy the string of characters after /folders/ in the URL.
AutoCrat handles the mail-merge step that generates the individual profile documents. See the in-app Help dialog (Get Picture Hyperlinks → Profiles Help) for full AutoCrat setup instructions.
- Open the bound Google Sheet.
- Click Get Picture Hyperlinks → Import Picture Hyperlinks.
- After the script completes, run your AutoCrat job to generate the profile documents.
This script does not use Script Properties. Folder IDs are read directly from the Setup sheet at runtime, keeping configuration visible and editable without touching the script.
No triggers are required. The script runs on demand from the custom menu. The onOpen() function runs automatically when the spreadsheet is opened to create the menu — this is a built-in Apps Script trigger that requires no manual setup.
excel-student-profiles-project/
├── src/
│ ├── Code.js # Main script
│ ├── Help.html # In-app help dialog
│ └── appsscript.json # Apps Script manifest
├── .clasp.json # clasp config (not committed — see .gitignore)
├── .claspignore # Controls which files clasp pushes
├── .gitignore
├── LICENSE
└── README.md
Originally created for Northside Independent School District (NISD). Generalized and released for public use upon retirement.
MIT © 2025 Alvaro Gomez