FridayPush opened a new issue, #44628:
URL: https://github.com/apache/airflow/issues/44628
### Apache Airflow Provider(s)
google
### Versions of Apache Airflow Providers
apache-airflow-providers-google==10.22.0
### Apache Airflow version
2.10.1
### Operating System
Linux
### Deployment
Amazon (AWS) MWAA
### Deployment details
_No response_
### What happened
When using the `SQLToGoogleSheets` operator data that is null is incorrectly
updated in the Google Sheet; primarily if pre-existing data is in place.
### What you think should happen instead
The Range of cells updated by the function should be cleared before
uploading the new values, so that null values are updated correctly. For
example the following Operator resolves the issue.
```python
class SqlToGoogleSheetWithClear(SQLToGoogleSheetsOperator):
def execute(self, context: Any) -> None:
sheet_hook = GSheetsHook(
gcp_conn_id=self.gcp_conn_id,
delegate_to=self.delegate_to,
impersonation_chain=self.impersonation_chain,
)
sheet_hook.clear(
spreadsheet_id=self.spreadsheet_id,
range_=f"{self.spreadsheet_range}!A1:ZZ", #More complex logic
needed if not the raw sheet
)
super().execute(context)
```
### How to reproduce
To recreate: Populate a google sheet's First row so all columns have the
value `1`. In the operator add the query
```sql
select 2::int as id, null as name, 'William' as last_name
```
The google sheet update process sees the 'null' name column as empty and
doesn't update the existing cell populated with the value `1` resulting in:
```
id, name, last_name
2, 1, William
```
### Anything else
I'm willing to submit a PR but unsure the best approach to handle backwards
compatibility. An additional parameter could be included to truncate before
load, but I do believe this feels like a bug to me. If my SQL returns Null I
expect the cell's value to be empty on a successful run of the operator.
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of
Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
