I’m working with the Google Sheets API v4 and I’m stuck. My spreadsheet has three columns: dates and two text columns. I’m trying to upload data in bulk but I’m running into issues.
When I use valueInputOption = "RAW", the date column gets messed up. There’s this weird invisible apostrophe before each date. The text columns are fine though.
If I switch to valueInputOption = "USER_ENTERED", the dates look good, but now the text columns are being interpreted weirdly. Like, if I enter “5-6-7”, it might show up as a date instead.
What I really want is to have the date column treated as USER_ENTERED and the text columns as RAW. Is there a way to set different valueInputOption for different columns or cells? I’ve been scratching my head over this for hours. Any help would be awesome!
I’ve encountered similar issues when working with the Sheets API. Unfortunately, there’s no built-in way to set different input options per column. A workaround I’ve used is to split your data into separate update requests. First, update the date column using USER_ENTERED, then update the text columns using RAW. This approach requires more API calls but ensures each column is handled correctly. You’ll need to adjust your code to make multiple update requests, but it should solve the formatting problems you’re experiencing.
hey noah, i feel your pain! google sheets api can be tricky.
there is no direct way to set different input options for columns, but try formatting your dates as strings (like ‘2023-05-15’) before uploading with RAW. hope it helps!
I’ve dealt with this exact problem before, and it’s frustrating. Here’s what worked for me:
Instead of trying to set different input options, I pre-processed my data before sending it to the API. For dates, I formatted them as strings in the exact format Sheets expects (YYYY-MM-DD). Then I used valueInputOption = ‘RAW’ for everything.
This approach kept my dates correct and my text columns untouched. It required a bit more work on my end, but it was way more reliable than fighting with the API’s input options.
If you’re dealing with a lot of data, you might want to look into using batch updates. It’s more efficient and gives you more control over how each piece of data is handled.
Hope this helps you out. API work can be a real headache sometimes!