2

Here's my setup and what I want to accomplish:

  • I currently have a Google Form set up for users to submit information, which is then logged in a Google Sheet.
  • I then, use PyDrive to save the contents of the Google Sheet as a csv.
  • Pandas then reads that csv, and deletes data within the csv based on certain criteria.
  • After that, I use PyDrive to re-upload the "fixed" csv to Google sheets, saving over the old one.

Here's the problem:

whenever I do this, it terminates the link between the Google Sheet and Google Form. Is there some way I can preserve this link or re-link the Form and the Sheet?

Here is the snippet of code I have been playing with:

submissions_data = drive.CreateFile({'id': ext['SUBMISSIONS_ID']})
submissions_data.GetContentFile("data.csv", mimetype = "text/csv")
df = pd.read_csv("data.csv")
df.drop([0],inplace=True)
df.to_csv("data.csv", index=False)
submissions_data.SetContentFile("data.csv")`

Preferably, I would like to find a way to do this using PyDrive, but anything will work at this point. Any help is appreciated! Thank you!

Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449
james
  • 343
  • 1
  • 2
  • 10
  • are you creating a new file rather than updating the existing file? – Linda Lawton - DaImTo Aug 03 '20 at 08:52
  • @DaImTo I'm sure I am, but I think that is the closest I can get to updating a file according to [PyDrive's documentation](https://pythonhosted.org/PyDrive/filemanagement.html#upload-and-update-file-content) – james Aug 03 '20 at 16:32

1 Answers1

3

Use Google Drive API with oauth2client and gspread to update the existing spreadsheet directly.

  1. Go to the Google APIs Console. Create a new project. Click Enable API.
  2. Enable the Google Drive API. Create credentials for a Web Server to access Application Data.
  3. Name the service account and grant it a Project Role of Editor.
  4. Download the JSON file.
  5. Copy the JSON file to your code directory and rename it to client_secret.json
  6. Find the client_email inside client_secret.json. Back in your spreadsheet, click the Share button in the top right, and paste the client email into the People field to give it edit rights. Hit Send.
pip install gspread oauth2client

#spreadsheet.py
import gspread
from oauth2client.service_account import ServiceAccountCredentials


# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds']
creds =   ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)

# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
sheet = client.open("Copy of Legislators 2017").sheet1

# Extract and print all of the values
list_of_hashes = sheet.get_all_records()
print(list_of_hashes)

You can write to the spreadsheet by changing a specific cell:

sheet.update_cell(1, 1, "I just wrote to a spreadsheet using Python!")

Or you can insert a row in the spreadsheet:

row = ["I'm","inserting","a","row","into","a,","Spreadsheet","with","Python"]
index = 1
sheet.insert_row(row, index)

Check the gspread API reference for the full details on these functions along with a few dozen others.

Glasgow
  • 94
  • 7