There is a known issue that effects a small percentage of users where their date values will be treated as string values, leading date values to be entered into the cell as:
This is an issue for users that rely on formulas that expect that value to be handled as a date, and the apostrophe will lead to calculation failures.
We don't know specifically what causes this for some users. It seems to be related to those who use the trigger system as manually refreshed queries often don't have this issue. We have been investigating the cause to try to find a permanent solution, but at this time there is no resolution.
There are a couple of different work-arounds that may help with the situation. Please check and try each of the following to see if it helps with keeping the apostrophe character out of the date values.
Check Cell Formatting
In some cases, the apostrophe is added due the Sheets cell format settings. To prevent this cause, check and ensure the cell format settings for the whole column or row that may contain dates are set to a "date" format type rather than plain text or automatic.
The advanced setting TIME_DIMENSION_FORMAT can sometimes help prevent the apostrophe issue. See this guide for how to use this setting. You will want to set the date to match the same format as you wanted it to appear in normal. It's not actually changing the format but trying to enforce the behavior of treating it as a date and not a string.
The advanced setting DISABLE_DIMENSION_AUTOFORMAT may prevent the apostrophe from being added by Sheets as it prevents the mechanism for Sheets to auto-format the values coming in from the connector. You can use this setting by adding the string DISABLE_DIMENSION_AUTOFORMAT to the Advanced settings text box.
NOTE - This setting is not supported by new backend trigger system so it doesn't help in that case. So please try one of the other options if you know your are using that version of the trigger system.
Run the Refresh Manually From the Sidebar
This is the only work-around method known to 100% work to remove the apostrophes and not require hand-editing them out. But it's also somewhat manual. If you see them in your date values, place the cursor in the cell with the query and then open the sidebar. You should then get the option to Refresh the query there. Click that button and the apostrophes will go away.
They may come back if you modify the query and use Apply changes or if the query is updated via a trigger. Repeating the above to use the sidebar Refresh button should remove them again.
This method also works well with the TIME_DIMENSION_FORMAT noted above.