Google Sheets API adding single qoute to text

Question

Hi I am running a Google APi script to enter data into a sheet but for some text (number,dates, booleans) it's adding a ' before e.g 05/01/2021 = '05/01/2021

I am using batch update:

def turn_into_range(data, SheetId, row,cols):
    rows = [{'values': [{'userEnteredValue': {'stringValue': f}} for f in e]} for e in data]
    rng = {'sheetId': SheetId, 'startRowIndex': 0, 'startColumnIndex': 0}
    fields = 'userEnteredValue'
    body = {'requests': [{'updateCells': {'rows': rows, 'range': rng, 'fields': fields}},{
            "updateSheetProperties": {
                "properties": {
                    "gridProperties": {
                        "rowCount": row + 1,
                        "columnCount": cols
                    },
                    "sheetId": SheetId
                },
                "fields": "gridProperties"
            },
        
        }
                         ]}
    clean_dict = simplejson.loads(simplejson.dumps(body, ignore_nan=True))
    return clean_dict

def post_sheet(service_sheets, spreadsheet_id, body):
    request = service_sheets.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body)
    response = request.execute()
    return response

any ideas?

Answer 1

For this issue, it is because your data is treated as string in stringValue.

You need to use other types of values for different data types,

stringValue:

• "A String", # Represents a string value.
• Leading single quotes are not included. For example, if the user typed '123 into the UI, this would be represented as a stringValue of "123".

boolValue:

• True or False, # Represents a boolean value.

numberValue:

• 3.14, # Represents a double value.
• Note: Dates, Times and DateTimes are represented as doubles in "serial number" format.

formulaValue:

• "A String", # Represents a formula.

errorValue:

• An error in a cell. Represents an error.
• This field is read-only.

Try adding this in your code.

from dateutil.parser import parse

def checkData(data):
    if (isinstance(data, bool)):
        return 'boolValue'
    try:
        if (isinstance(data, (int, float)) or parse(data)):
            return 'numberValue'
    except ValueError:
        return 'stringValue'

Behaviour:

Now, use it in your code:

rows = [{'values': [{'userEnteredValue': {checkData(f): f}} for f in e]} for e in data]

For more details, documentation can be found here