Upload CSV file from local PC to Google Sheet using Google Apps Script

Question

I have a folder on my local PC containing multiple CSV files. Files are named according to the date they were generated. Is there any way that we can get todays' date file (12/27/2021.csv) which is located in the CSV folder and import it into a google sheet?. There is this python resource which somewhat lets you do that but it is not what is needed.

I have used this google apps script code for importing CSV file from Google Drive to Google sheet:

function loadCSVFilesIntoSheets() { 
  var ss=SpreadsheetApp.openById('SpreadsheetId')
  var folder=DriveApp.getFolderById('folderId');
  var files=folder.getFilesByType(MimeType.CSV);
  while(files.hasNext()) {
    var file=files.next();
    var vA=Utilities.parseCsv(file.getBlob().getDataAsString());
    var ts=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yy HH:mm:ss")
    var sh=ss.insertSheet(file.getName()+'-'+ts);
    sh.getRange(1,1,vA.length,vA[0].length).setValues(vA);
  }
}

I have tried to use this Google sheet formula by providing a CSV file path but it gives an error because this path is not publicly accessible.

IMPORTDATA("file:///C:/Users/myUserName/myFolder/12/27/2021.csv")

Can we upload a CSV file that is currently in the PC folder to Google sheet using google apps script?

You can publish your apps script as web app, and then use it to upload local files, for reference see this tutorial https://tanaikech.github.io/2018/12/22/uploading-multiple-files-from-local-to-google-drive-using-google-apps-script/ and https://stackoverflow.com/questions/42217052/how-to-upload-a-file-via-post-dopost-to-a-google-scripts-web-app — Kos, Dec 27 '21 at 09:59
Yes, I read both sources. But the issue is CSV files should be automatically uploaded not by selecting the file manually. — , Dec 27 '21 at 10:04
You can write bash script for calling your web app URL, but it's not related to Apps Script anyway, as it's run on Google servers, like always — Kos, Dec 27 '21 at 10:14
There are other tools that may sync your local files to Google Drive — Kos, Dec 27 '21 at 10:27
You have to create the connection between google and your local computer — Cooper, Dec 27 '21 at 15:03

Emel · Answer 1 · 2021-12-27T12:21:52.073

As an alternative to @Kos' suggestion, you can create a script and run it daily.

For this example I assume you have all the files inside a folder named csv and the format of the names will be 27_12_2021.csv, for example.

The steps will be:

Follow the steps mentioned in Python Quickstart to get the drive_service working. Note: You must change the scope from drive.metadata.readonly to drive or another one adjusted to your needs.
Create a script and put this code. Take into account that drive_service() is the service builded from quickstart:

`upload.py`

def get_today():
    today = datetime.datetime.now()
    day = today.strftime('%d')
    month = today.strftime('%m')
    year = today.strftime('%Y')
    return "{}_{}_{}".format(day, month, year)


for file in scandir('./csv'):
    if(file.name.split('.')[0] == get_today()):
        file_metadata = {'name': str(get_today()+".csv")}
        media = MediaFileUpload(
            './csv/{}'.format(file.name), mimetype='text/csv')
        file = drive().files().create(body=file_metadata,
                                      media_body=media,
                                      fields='id').execute()
        print(file)

Schedule the scrpit to run daily. I leave you examples in Linux using crontab and Windows one.

This script is just an example of how it can be achieved, you must adapt it to your needs.

Documentation:

Drive's PyDoc API documentation.

Upload CSV file from local PC to Google Sheet using Google Apps Script

1 Answers1

upload.py

Documentation:

`upload.py`