Keeping a Google Sheet Updated with the most recent version of a csv file

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.

  1. Every night, PowerSchool creates a csv file that contains all of the attendance data, and then uploads that data to our server.
  2. Our server uses a script to upload that file to a folder in Google Drive.
  3. (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.

Take a look at Google Apps Script (GAS), and consider a time based trigger from the script run options.

You should be able to clear and then import to a sheet with a script.

Holy crap how did I not know this was a thing!!!

EDIT: This is exactly what I needed and my workflow is finished. Thank you so much!

Huge fan of GAS! I have several automations with GAS that I could not live without! The hardest part about working with GAS is the online script editor. I generally develop in Sublime and then prettify code (in Sublime) and then copy/paste into script editor.