We have a flow that grabs information from a database and populates a google sheets page. Some of the output fields can be empty. In that case, we don’t want to override the sheets cell value.
For example, if the sheets cell has the value “application” and the output value from the flow is “”, we want the sheets cell value to remain as “application”. But if the value is not empty, we DO want to override the value.
We are not sure what ‘script’ to put in the “values to send” fields of the Google Sheets node.
There is no built-in functionality to not update the value if it is an empty string or even null. You need to remove undesired fields.
There is also no way to dynamically control not to update a field in the table if there is no field in the record input. So, you need to read existing data, combine with new data following the rules you want and update the record in its entirety.
If the suggestion above resolves your question, please mark this post as a Solution.
I don’t think there is a way to achieve that when mapping fields ‘manually’.
Even if you write an expression to check the source value and replace it with undefined/null, n8n still sends empty strings and clears the corresponding sheet cells:
To workaraound this I ended up writing a simple JS script to drop any empty keys before the Sheets node and configured the Sheets mapping to "Map Automatically’
This has the downside that your source data keys have to match the sheet columns exactly but it works.
Here is my script that drops the empty keys:
// Loop over input items
for (const item of $input.all()) {
// Get the JSON object of the current item
const json = item.json;
const keysToRemove = [];
// Loop through the keys of the JSON object
for (const key in json) {
// Check if the value of the current key is empty or undefined
if (json[key] === '' || typeof json[key] === 'undefined' || json[key] === null) {
// If it's empty or undefined, mark the key for removal
keysToRemove.push(key);
}
}
// Remove the marked keys from the JSON object
for (const key of keysToRemove) {
delete json[key];
}
}
// Return the modified input items
return $input.all();
And here is an example of how to use it in a workflow:
If you find my answer helpful and if it resolves you issue please don’t forget to mark it as the solution
Solution.
