9

I have three columns in my spreadsheet. The first one is date, the second and the third are simple strings.

When I do batch upload of my data valueInputOption = "RAW" I get a wrong result for my Date column. All dates have an invisible apostrophe before them. String columns are OK.

When I use valueInputOption = "USER_ENTERED" all dates are good, but other values are interpreted not as actual values but as Google parsed values. For example a string "2-3-4" will be represented as 02/04/2004 or something like that.

I want data column to be represented as USER_ENTERED and string columns as RAW.

Is there any way to set valueInputOption for a cell (column)?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
yurart
  • 593
  • 1
  • 6
  • 23

4 Answers4

13

I have found the answer.

In order to make all needeed data to be represented as string, you should:

  • set valueInputOption = USER_ENTERED;
  • prepend it with apostrophe sign just like this one '.

In this case other columns type will be determined automatically based on Google's own algorithms and columns with cells prepended with ' sign will be shown exactly as you enteren them

It works when all spreadsheet columns have Automatic type set. Not sure what will happen for colums with other format types.

Antony Hatchkins
  • 31,947
  • 10
  • 111
  • 111
yurart
  • 593
  • 1
  • 6
  • 23
1

It's best to show this in practice in code

SERVICE_ACCOUNT_FILE = "wydupa15.json"
SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
SAMPLE_SPREADSHEET_ID = "1oEfhwe46ZPPpBgr_LYfVZAzJWV-enZHfW8Tp2RsYhvQ"

CRED = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES
)

service = discovery.build('sheets', 'v4', credentials=credentials)

# The A1 notation of the values to update.
RANGE = 'Test1!A5:C5'  # TODO: Update placeholder value.

result = service.spreadsheets().values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=RANGE).execute()

print("Current values in a given area:",result)

# ---inserting DATA - GET function ----------------------------------------------

values = result.get('values', [])

AAA = [['A', 'b', 5220]]

request = service.spreadsheets().values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                                                 range=RANGE,
                                                 valueInputOption='USER_ENTERED',
                                                 body={'values':AAA})
response = request.execute()

# ---------------------------------result------
result = service.spreadsheets().values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=RANGE).execute()

print("Area value after changes:",result)
Wojciech Moszczyński
  • 2,893
  • 21
  • 27
0

Try valueInputOption like this in google REST API.

const headers = new HttpHeaders({ "Authorization": `Bearer <token>`, "Content-Type": "application/json" });
   
const url = `https://sheets.googleapis.com/v4/spreadsheets/<spreadsheetid>/values/<range>:append?valueInputOption=RAW`;
    const data = {
      "range": "<range>",
      "majorDimension": "ROWS",
      "values": [["Row 1 Col 1", "Row 1 Col 2"]]
    }
this.httpClient.post(url, data, { headers: headers });
Shirantha Madusanka
  • 1,461
  • 1
  • 11
  • 16
0

None of these answers were adequate for my situation. See my solution (which is a little specific to the gspread library I was using, but you can adapt it) at https://github.com/burnash/gspread/issues/524#issuecomment-1229082678. Copied below:

I have various kinds of data:

  • zipcodes, which look like ints but can be prefixed with 0s, so therefore need to be stored as strings eg "00123"
  • booleans, eg True
  • floats, eg 3.0

I was finding that if I used value_input_option="RAW", then I would get the problems described here: all of the zipcodes, booleans, and floats (eg anything that google sheets is able to "cast" from a string to a better datatype) would all get that annoying leading apostrophe (which I'm guessing is a sheets notation to say "don't cast this").

OK, so then I tried value_input_option="USER_ENTERED". Now, the leading apostrophe is gone, but the casting is happening. So 00123 -> 123, True -> TRUE, and 3.0 -> 3. That also wasn't OK for my needs. This even happens if I pre-cast my values to strings, eg if I upload the string "3.0" instead of the float 3.0.

So, what I ended up doing was first pre-casting to strings, so I could get exactly what I wanted, and then using:

def set_sheet_values(worksheet: gspread.Worksheet, data: list[list[str]]):
    # The .format() only affects existing cells, so if your data is larger than
    # the current sheet, those newly added cells won't be affected.
    # Therefore, resize before formatting so all columns will be affected.
    worksheet.resize(rows=len(data), cols=len(data[0]))
    # This is equivalent to in the web UI selecting all cells and
    # then in the menu going `Format` > `Number` > `Plain Text`
    # For more info see
    # https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells?hl=en#cellformat
    worksheet.format(
        ":",
        {
            "numberFormat": {
                "type": "TEXT",
            },
        },
    )
    # Act as though the user just typed this in the web UI
    # Because of the formatting, no "casting" happens
    worksheet.update(data, value_input_option="USER_ENTERED")
Nick Crews
  • 837
  • 10
  • 13