3

I have a python code which uses drive and sheet api to list the files inside of a folder. I have multiple google sheets inside this folder and some of them have spaces in between texts, like the one given in the image. I wanted to change the text wrap to overflow for all the cells i.e sheet object in python using google sheet api. I can see there is a way (wrap_strategy) to set it to overflow_cell, but I don't know how to use it. Can anyone please help in this case?

I can see the documentation in apps script but not using python.

def process_file(file):
    file["name"] = file["name"] + '.' + file["id"] #prefix the file name 
    print(file["name"])
sheet = open_sheet(file['id'])
if sheet is None:
    print("Unable to open sheet: " + file['id'])
    return

The actual result would format all google sheets inside this folder with text formatting as overflow for all the cells

behold
  • 538
  • 5
  • 19
Prajakta Yerpude
  • 215
  • 6
  • 20

2 Answers2

4
  • You want to set the wrap strategy of a sheet on Spreadsheet.
  • You want to set the wrap strategy of all cells of the sheet as "overflow".
  • You want to achieve this using gspread.

From your question and tag, I understood like above. If my understanding is correct, how about this sample script?

In this sample script, it supposes that the wrap strategy of "overflow" is set to all cells of "Sheet1". When batch_update() is used at gspread, the request body is required to be created.

Sample script:

spreadsheetId = "###"  # Please set this
sheetName = "Sheet1"  # Please set this

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
sheetId = ss.worksheet(sheetName)._properties['sheetId']
body = {
  "requests": [
    {
      "updateCells": {
        "range": {
          "sheetId": sheetId,
          "startRowIndex": 0,
          "startColumnIndex": 0
        },
        "rows": [
          {
            "values": [
              {
                "userEnteredFormat": {
                  "wrapStrategy": "OVERFLOW_CELL"
                }
              }
            ]
          }
        ],
        "fields": "userEnteredFormat.wrapStrategy"
      }
    }
  ]
}
res = spreadsheet.batch_update(body)

Note:

  • This sample script supposes that you have already been able to read and write the Spreadsheet using Sheets API.
  • Unfortunately, I couldn't understand about your script. I'm sorry for this situation.

References:

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

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • 1
    This is perfect! Thank you so so much for the answer. Sorry if you got confused by the question, yes I am able to read sheets, download its data by traversing into a google drive folder. One issue with the download of sheet was when there was text wrap in a cell, sheet api downloaded the first line properly but the next lines were shifted in the next row causing shift in cells. This wrap strategy will help in this case since overflow will make everything in one line and the download will be easy. (I am downloading almost 4500 google sheets and consolidating it in a single table) Thanks alot :) – Prajakta Yerpude May 03 '19 at 13:39
  • @Prajakta Yerpude Thank you for replying and the detail explanation. I could understand about your situation. And I'm glad your issue was resolved. Thank you, too. – Tanaike May 03 '19 at 23:14
  • I have one more use case :( , even if a column has overflow strategy and someone accidently adds a new line character in a cell , is there any way we can remove this in a cell using this code? Like for example, given overflow strategy, the user writes in a single cell as .. (line 1 : abcdefg line /n 2: hijklmn) this still cause an issue in my download causing shift in cells. Please help, and thanks a lot. – Prajakta Yerpude May 05 '19 at 17:08
  • you can check my code here : https://github.com/pyerpude/Google-APIs-in-Python/blob/master/scanDriveFolder_google_sheet_test.py The sample image of a sheet causing problem with new line chars : https://github.com/pyerpude/Google-APIs-in-Python/blob/master/sample%20sheet.PNG – Prajakta Yerpude May 05 '19 at 17:37
  • Got the trick ;D Added this line : all_values = [[s.encode("utf-8").replace("\n"," ").replace("\r"," ") for s in nested] for nested in all_values] – Prajakta Yerpude May 05 '19 at 18:29
  • 1
    @Prajakta Yerpude I'm glad your new issue was resolved. – Tanaike May 05 '19 at 23:09
1

Why not use gspread formatting, it is a simple command:

from gspread_formatting import *

worksheet.format("A1:D10", {"wrapStrategy": "OVERFLOW_CELL"})

In my case I was looking how to set it to WRAP because it was overflow by default...

Ecuador
  • 1,014
  • 9
  • 26
  • I believe your example doesn't actually require gspread_formatting; it should work fine with just gspread, as it closely matches this syntax: https://docs.gspread.org/en/latest/user-guide.html#formatting – KBurchfiel Jul 27 '23 at 19:26