How to update google drive files

Question

So this is what i'm trying to do:

data = drive.files().get_media(fileId=fileId).execute()
csvData = [row.split(",") for row in str(data, 'latin-1').split("\n")]
ar = []
temp = []
for i, row in enumerate(csvData):
    if "".join(row) != "":
        temp.append(row)
    else:
        ar.append(temp)
        temp = []
    if i == len(csvData) - 1:
        ar.append(temp)

# Create request body for the method of spreadsheets.create of Sheets API. [Tanaike]
sheetsObj = []
for sheet in ar:
    tempRow = []
    for row in sheet:
        tempCol = []
        for col in row:
            tempCol.append({"userEnteredValue": {"stringValue": col}})
        if len(tempCol) != 0:
            tempRow.append({"values": tempCol})
    if len(tempRow) != 0:
        sheetsObj.append({"data": [{"rowData": tempRow}]})

# Request to Sheets API. [Tanaike]
body = {"properties": {"title": "spreadsheetTitle"}, "sheets": sheetsObj}
res = sheets.spreadsheets().create(body=body).execute()
print(res)

I recently asked a question regarding an upload of files to google drive via google API. I had success with the code, but now I realized I need a separate code that, instead of uploading the data into a new file, updates an existing google sheets.

I've been struggling against batchUpdate() (https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate), because i don't know how to fit my old solution for sheetsObj into it.

The idea is the same as here: Google docs formatting, converting texts into sheets

I wish to update an existing google sheets file with data in .txt format, but instead of simply uploading and thus creating new files, I wish to update the existing google sheets files I currently have.

• The idea is to overwrite whatever is in the existing files. Assume that the file id is known.

---

ps.: There is something weird going on which I didn't notice earlier: all the cells start with a ', I'm trying to deal with it, but if someone figures it out before I do, please include it in the solution.

Answer 1

• You want to update the existing Spreadsheet using the CSV data from the following values. The values are from Google docs formatting, converting texts into sheets

  ID, NAME, MOBILE, CITY, COUNTRY, BIRTHDAY, 
  3, NameGoesHere1, 21 98658 5548, abcity, countryNameHere, 1998-05-02, 
  6, SomeoneElse Joined Here, 21 98535 1218, whereland, Far far away, 1989-11-15, 
  5, AnotherCustomer, 21 85482 5245, somecity, Somewhereland, 1999-08-04, 
  
  ID, PRICE, STOCK, ASDF, BASDF, CASDF,
  
  ID, NAME, PRICE, DESCRIPTION, 
  2, pen, 1.5, The pen is mightier than the sword, 
  3, pencil, 1.0, Can be used to write, 
  4, RPG, 150.0, well that escalated quickly, huh, 
  
  EMPTY, 
  names, 
  goofs, 
  
  ID, FLAVOR, 
  

• The existing Spreadsheet has 5 sheets for updating using the CSV data for 5 sheets.

• You want to achieve this using google-api-python-client with python.
• You have already been able to get and put values for Spreadsheet using Sheets API.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

1. Retrieve the values from the CSV file.
2. Parse the values for each sheet, and create the request body.
3. Request to the existing Spreadsheet with the created request body.
   • In this case, I used the method of spreadsheets.values.batchUpdate.

Modified script:

Before you run the script, please set the variables of csvFileId, spreadsheetId and sheetNames.

csvFileId = '###'  # Please set the CSV file ID.
spreadsheetId = '###'  # Please set the Spreadsheet ID.
sheetNames = ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5']  # Please set the sheet names in the Spreadsheet for updating.

sheets = build('sheets', 'v4', credentials=creds)
drive = build('drive', 'v3', credentials=creds)

# Retrieve data from Google Drive and parse data as an array.
data = drive.files().get_media(fileId=csvFileId).execute()
csvData = [row.split(",") for row in str(data, 'utf-8').split("\n")]
ar = []
temp = []
for i, row in enumerate(csvData):
    if "".join(row) != "":
        row = [v.strip() for v in row]
        temp.append(row)
    else:
        ar.append(temp)
        temp = []
    if i == len(csvData) - 1:
        ar.append(temp)

valuesUpdateReq = []
for i, sheet in enumerate(ar):
    if bool(sheet):
        sheetName = sheetNames[i]
        valuesUpdateReq.append({"values": sheet, "range": sheetName, "majorDimension": "ROWS"})

# Request to Sheets API.
batch_update_values_request_body = {"data": valuesUpdateReq, "valueInputOption": "USER_ENTERED"}
res = sheets.spreadsheets().values().batchUpdate(spreadsheetId=spreadsheetId, body=batch_update_values_request_body).execute()
print(res)

Note:

• In above script, the values are put to the Spreadsheet with "USER_ENTERED". By this, the values can be parsed as the string, number and date. And also, the single quote of the top of character is not used.

Reference:

• Method: spreadsheets.values.batchUpdate

If I misunderstood your question and this was not the direction you want, I apologize.