CODEWITHSUNDEEP
pythongoogle-sheets-apigspread
strg
strg

Reputation: 116

Changing column format in google sheets by gspread and google sheets API

I am using gspread and I am looking for a proper way to changing column format by script. I have a column with time durations. I would like to change format of entire column to duration. In Google UI I can mark entire column then click format, then number and set duration. Is it possible to do it by gspread/google sheets API?

EDIT

client = gspread.authorize(credentials)

try:
  sheet = client.open(sys.argv[1]).sheet1
except (gspread.SpreadsheetNotFound, IndexError):
  print("Spreadsheet not found")
  sys.exit()

try:
  tags = sheet.col_values(13)
  tags.remove('Tags')
  unique_tags = list(dict.fromkeys(tags))
except ValueError:
  print("Your spreadsheet cannot be modified and should contain original 
  columns from toggle reports.")
  sys.exit()


START_INDEX = 7
sheet.update_cell(6, 15, "SUM")
for tag in unique_tags:
  sheet.update_cell(START_INDEX, 15, tag)
  sheet.update_cell(START_INDEX, 16, "=SUMIF(M2:M; " + '"' + tag + '"' + "; 
  L2:L)")
  START_INDEX += 1

sheet.update_cell(6, 16, "=SUM(P7:P15)")

Upvotes: 2

Views: 4745

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • You want to change the format of a column on Google Spreadsheet.
    • In your situation, there are the values of the time duration in a column. You want to change the format of the time duration.
  • You want to achieve this using gspread with python.
  • You have already been able to get and put values for Spreadsheet using Sheets API with gspread.

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

Modification point:

  • In order to change the format of the column, the method of spreadsheets.batchUpdate in Sheets API and "RepeatCellRequest" of the method of spreadsheets.batchUpdate are used. At gspread, the method of spreadsheets.batchUpdate can be used as batch_update(body).

Modified script:

In this modification, the top part of script in your question is used.

try:
    spreadsheet = client.open(sys.argv[1])  # Modified
    sheet = spreadsheet.sheet1  # Added
except (gspread.SpreadsheetNotFound, IndexError):
    print("Spreadsheet not found")
    sys.exit()

# I added below script.
requests = [{
    "repeatCell": {
        "range": {
            "startColumnIndex": 0,
            "endColumnIndex": 1,
            "sheetId": sheet._properties['sheetId']
        },
        "cell": {
            "userEnteredFormat": {
                "numberFormat": {
                    "type": "TIME",
                    "pattern": "[m]:s"
                }
            }
        },
        "fields": "userEnteredFormat.numberFormat"
    }
}]
body = {
    'requests': requests
}
res = spreadsheet.batch_update(body)
print(res)
  • As a test case, when the row 1 of the column "A" (the cell "A1") has 1:23:45 as the time duration, when above script (the format pattern is [m]:s.) is run, 1:23:45 becomes 83:45.
    • By the way, the format pattern of 1:23:45 as the time duration is [h]:mm:ss.
  • If the format pattern is [s], 1:23:45 becomes 5025.

Note:

  • When you use this script, I recommend to use a sample Spreadsheet. Because the format is changed by the script.
  • In above modified script, the time duration format of the column "A" on the 1st tab in the spreadsheet name of sys.argv[1] is changed to [m]:s.
  • This is a simple sample script. So please modify it for your actual situation. Especially, please modify the format pattern for your actual situation. Because I'm not sure about your actual situation.

References:

  • batch_update(body)
  • Method: spreadsheets.batchUpdate
  • RepeatCellRequest

  • Date and Number Formats

  • For the pattern of time duration, the official document says as follows.

    • [h+]: Number of elapsed hours in a time duration. Number of letters indicates minimum number of digits (adds leading 0s).
    • [m+]: Number of elapsed minutes in a time duration. Number of letters indicates minimum number of digits (adds leading 0s).
    • [s+]: Number of elapsed seconds in a time duration. Number of letters indicates minimum number of digits (adds leading 0s).

Upvotes: 2

Related Questions