Hello there,
I am trying to read data from a Google Sheet to load it into a Redshift table. The Sheet has seven columns and currently, there is data in only the first three. The “Google Sheet Get Values” step retrieves only the non-null values and is connected to a “PostgreSQL Insert Row” step downstream. The workflow throws an error when I use the “{{steps.get_values}}” in the “Row Values” as there is a mismatch between the total columns to insert and the columns being returned (i.e, 7 vs 3). What’s the best way to fetch all the records from the Sheet, including the empty cells?
You may need to use Javascript or Python to create empty values. Here’s an example with Node.js:
// A Node.js step to compare an array of strings vs a number of columns needed, fill the array with empty values if short of values from the rows
export default defineComponent({
props: {
rows: {
type: 'string[]',
label: "Rows",
description: "Rows with missing values to be expanded"
},
numColsNeeded: {
type: 'integer',
label: "Number of columns needed",
description: "Number of columns needed"
}
},
async run({ steps, $ }) {
const difference = this.numColsNeeded - this.rows.length;
const toBeAdded = Array(difference).fill('');
// Return data to use it in future steps
return [...this.rows, ...toBeAdded];
},
})
Thank you @pierce,
I am almost there and have just another question. Should I iterate over the rows in the Node JS step or is there a way to fetch all the rows at once? That is to say, the value “{{steps.get_values_in_range.$return_value[0]}}” in the “Rows” field is working but not “{{steps.get_values_in_range.$return_value}}”.
No you’re correct, {{steps.get_values_in_range.$return_value}} uses the entire array as input for the step, whereas {{steps.get_values_in_range.$return_value[0]}} only uses the first entry in your array.
Can you explain or share a screenshot of what happens when you try to use the entire array as input? ({{steps.get_values_in_range.$return_value}})
