Publish python dataframe to Gsheet: Sketch borders around a set of data based on dates

Question

I have a dataframe df (it is a time series data) as shown in attached photo. I am able to write this dataframe to the Google Sheet using the code below.

Questions:

1. In Google Sheet, I would like to draw a horizontal line after each Week ends (shown in photo).
2. As you can see, I calculate the Year# and Week# (from the Date column). Can the unique combinations of these two columns be used to identify data pertaining to the same week and be used as logic to sketch a line?

Clear the Google Sheet:

service.spreadsheets().values().clear(
    spreadsheetId=spreadsheet_id,
    range='A1:AZ2600',
    body={}
).execute()

Write df_final to Google Sheet:

cell_range_insert= 'B7'
values = df_final.to_json()
body = {'values': values}
response_date= service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    valueInputOption='RAW',
    range=cell_range_insert,
    body=dict(
        majorDimension= 'ROWS',
        values=df_final.T.reset_index().T.values.tolist()
    )
).execute()

Answer 1

I believe your goal is as follows.

• You want to insert the horizontal line every each Week ends to the Spreadsheet.
• When the script is run, after the script of "Clear the Google Sheet:" was run, the script of "Write df_final to Google Sheet:" is run.
• The data is put from the cell "B7" in the 1st tab.
• You want to achieve this using googleapis for python.

In your script, how about the following modified script?

Modified script:

Please set the values of spreadsheet_id and sheet_id.

spreadsheet_id = "###" # Please set Spreadsheet ID.
sheet_id = 0  # Please set the sheet ID.

# 1. Clear sheet. In this case, the values and the horizontal lines are removed.
service.spreadsheets().batchUpdate(
    spreadsheetId=spreadsheet_id, body={"requests": [{
        "repeatCell": {
            "range": {
                "sheetId": sheet_id
            },
            "fields": "userEnteredValue,userEnteredFormat.borders"
        }
    }
    ]}).execute()

# 2. Put the values from the dataframe to Spreadsheet.
cell_range_insert = 'B7'
# values = df_final.to_json() # It seems that this is not used.
# body = {'values': values} # It seems that this is not used.
response_date = service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    valueInputOption='RAW',
    range=cell_range_insert,
    body=dict(
        majorDimension='ROWS',
        values=df_final.T.reset_index().T.values.tolist()
    )
).execute()

# 3. Set the horizontal lines.
temp = -1
n = []
for index, row in df_final.iloc[:, 5:8].iterrows():
    s = ''.join(row.astype(str).tolist())
    if temp != s:
        n.append(index)
        temp = s
offset = 7
requests = [{
    "repeatCell": {
        "cell": {
            "userEnteredFormat": {
                "borders": {
                    "top": {
                        "style": "SOLID_THICK"
                    }
                }
            }
        },
        "range": {
            "sheetId": sheet_id,
            "startRowIndex": e + offset,
            "endRowIndex": e + 1 + offset,
            "startColumnIndex": 1,
            "endColumnIndex": 10
        },
        "fields": "userEnteredFormat.borders"
    }
} for e in n]
service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body={"requests": requests}).execute()

• When this script is run, the sheet is cleared using the batchUpdate method. In this case, both the values and the horizontal lines are removed. And, the data is put to the Spreadsheet. And then, the horizontal lines (columns "B" to "J" for every weekend) are put using the batchUpdate method.

• In order to check the row numbers for putting the border, I used the values of columns "G", "H", "I".

• In this sample, I used SOLID_THICK as the border. If you want to change this, please change it. Ref

Note:

• The above script checks the columns "G", "H", "I". If you want to check only the column "I" instead of the columns "G", "H", "I", as a simple modification, please modify the above script as follows.

  • From

      for index, row in df_final.iloc[:, 5:8].iterrows():
    

  • To

      for index, row in df_final.iloc[:, 7:8].iterrows():
    

References:

• Method: spreadsheets.batchUpdate
• RepeatCellRequest