I work at a small private school and am working on automating our attendance tracking and reporting. All of our data is in PowerSchool, and due to a lot of technical details, the only way I can reliably automate getting the raw data out is by uploading a csv file into secure Google Cloud SFTP server. That raw data then needs to be made the first worksheet in a Google Sheet, as I have other sheets that process and format the data. Here is the basic outline of what I want to have happen.
- Every night, PowerSchool creates a csv file that contains all of the attendance data, and then uploads that data to our server.
- Our server uses a script to upload that file to a folder in Google Drive.
- (This is where I need help)Google Drive sees that there is an updated version of the raw attendance data file, and replaces the raw data worksheet with the updated data.
I don’t want to just append entries on the spreadsheet, because attendance data changes as parents bring in documentation for absences, so I have to replace all of the data.