Harmonizes course data between two Google Sheets with differing structures, effectively resolving data structure inconsistencies across multiple stakeholders in the process of checking.
- A Google account with access to Google Sheets.
- A foundational understanding of Google Sheets and Google Apps Script.
- Access a Sample Google Sheet: Begin by opening the Sample Google Sheet.
*Note: The samples provided are merely illustrative, showcasing course data at an educational institution.
- Copy the Google Sheet: Make a copy of the Google Sheet to create your personalized version.
- Authorization: The first time you run the script, it will ask for your permission to access certain data. If you're using a free Gmail account, you might see a warning that says
Not Verified by Google. Don't worry about this warning - you still need to give permission for the script to work. The script is yours alone, and it won't share any data outside of Google, unless you choose to share your Google Sheet. The only data it might send outside of Google is website status codes, for monitoring purposes.
This script aligns course offering data from a dataset to match the institution's database format.
How it works:
- Fetches data from the
'1_Offering Data by Program'sheet. - Filters out predefined course codes not needed for display (e.g., 'MXD304' and 'WNV327' in the sample sheet)
- Displays structured data (Course Code, Semester, Faculty) on the
'1_Organized Offering Data'sheet.
How to use it:
- Paste (*value only) given course offering data into the
'1_Offering Data by Program'sheet. - Click the button on the
'1_Organized Offering Data'sheet. - Structured course offering data will appear in columns A ~ C.
This script structures class schedule data from a given dataset to match the institution's database format.
How it works:
- Fetches data from the
'2-1_Schedule Data by Program'sheet. - Checks each cell against a specific pattern. Matches are stored, non-matches are stored as exceptions.
- Groups data based on course code and day, then merges and sorts them.
- Displays structured data on the
'2-1_Organized Schedule Data'sheet. Exceptions are listed in column G.
How to use it:
- Paste (*value only) given class schedule data into the
'2-1_Schedule Data by Program'sheet. - Click the button on the
'2-1_Organized Schedule Data'sheet. - Structured class schedule data will appear in columns A ~ E and G.
This script structures class schedule data from a given dataset to match the institution's database format.
How it works:
- Fetches data from the
'2-2_Schedule Data by Program'sheet. - Checks each cell against a specific pattern and stores the course schedule data.
- Groups data based on course code, day, and faculty, then merges and sorts them.
- Displays structured data on the
'2-2_Organized Schedule Data'sheet.
How to use it:
- Paste (*value only) given class schedule data into the
'2-2_Schedule Data by Program'sheet. - Click the button on the
'2-2_Organized Schedule Data'sheet. - Structured class schedule data will appear in columns A ~ G.
This script structures class schedule data from a given dataset to match the institution's database format.
How it works:
- Fetches data from the
'2-3_Schedule Data by Program'sheet. - Checks each cell against a specific pattern and stores the schedule data.
- This script checks the location of the target cell to determine the day of the week and the number of required records, then pushes the schedule record accordingly. For example, if the cell is C4, the script pushes two records, one for Monday and another for Wednesday. The script also verifies if the class code is in a list where a second record is not necessary, as the class is held only once per week.
- Displays structured data on the
'2-3_Organized Schedule Data'sheet. This script evaluates the data after displaying it on the sheet. If a value is found to be invalid, it is highlighted in red for easy identification.
How to use it:
- Enter class schedule data into the
'2-3_Schedule Data by Program'sheet. - Click the button on the
'2-3_Organized Schedule Data'sheet. - Structured class schedule data will appear in columns A ~ G.
Please note that your use of this website is subject to the Terms and Conditions.
You can find the source code for this tool on Github. Click HERE .
Last updated: May 5, 2024