After uploading a sheet from SAP to GoogleDrive, all data formats are automatically set to US-format (numbers, date and timefields, according to the user setting of the google account). But I need the data in a German format. After uploading, I start a batchUpdate of the sheet with sheets API (updateSpreadsheetProperties, locale de_DE) to set a German format to the whole sheet.
This works only partially. The number formats are converted correctly to a German format, but the date and time fields are still in US-format. What can I do? Additional Information: I don´t know, which columns of the sheet contains dates, numbers etc., the content is dynamic.
I think this issue occurs because Google Sheets handles “Text vs. Value” data differently during a locale shift. When you upload a sheet from SAP, Google Drive often interprets the date strings as static text rather than numerical date values.
Changing the locale via the API tells the sheet how to display dates and how to interpret new typing, but it does not retroactively re-parse text strings that were already imported in a different format.
Since your content is dynamic and you don’t know which columns contain dates, you should apply a “Force-Parse” logic.
Step 1: Set Locale (You are already doing this)
Keep your updateSpreadsheetProperties call to set locale: de_DE. This ensures that once the dates are recognised, they follow German conventions.
Step 2: Use FindReplaceRequest to Force Re-evaluation
You can “nudge” Google Sheets into re-parsing every cell in the sheet without knowing which ones are dates. A common trick is to search for the date separator (like / or -) and replace it with itself. This forces the Sheet to re-evaluate the “Text” as a “Value” under the new locale rules.
Step 3: Apply a Named User-Defined Format
Instead of relying on the default locale format (which can be stubborn), explicitly push a numberFormat to the entire range. Since you don’t know where the dates are, you can use a RepeatCellRequestformula across the whole sheet. Google Sheets is smart enough to apply “Date” formatting only to cells it recognises as numbers/dates.