Open Navigation

Manipulating data with scripts

As mentioned in Manipulating data with helpers, there are a number of ways of manipulating data. This short guide gives a simple example of doing so with the Script connector.

Using Core Connectors with Script

Here, we will be going through a workflow that takes sales data from a Google Sheets file and sends the details as a message on the #sales channel on Slack every Monday morning, so the sales team can see where they are against target for the month at the beginning of each week. This guide will once again be making use of the Loop Connector and the Data Storage Connector, but will mostly focus on using the Script Connector.

We will also make use of the process of reading a CSV file from Google Sheets using the CSV Reader connector. The guide will begin at the Get query results stage, where we retrieve the rows data from the CSV file. If you would like a guide on how to get to this stage, you can find it here.

1 - Loop through the CSV rows join them together with interpolation

The Google Sheets rows are presented as an array of objects called RowData, found in the output of the Get query results operation of the CSV Reader connector. Inside each object is another array of objects called Data, that contains the information we want.

rowData

Using the loop connector, we loop through RowData and will store each row as an element in our own array, which we will convert to a readable string later, that will compose the Slack message.

row-interpolation

As we are inside a loop, we need to make sure that we are not completely overriding the data in the Data Storage connectors each time around. To do so, we will get this data before we set it, to ensure that we are adding to the previous loop every time we set.

Set the first data storage connector's operation to Get value and name it's key - here, we are using sales. The first run will return no data, so we should set a default value. As mentioned previously, this will be an array that the rows will be stored within, so set the default value to an empty array.

get-empty-array

In the Script connector step, we will be taking the RowData we are looping over and convert it to a readable format, for example James: $50,900. To acheive this, we will set the relevant data from RowData as variables. Note that your input variables for the Script Connector will be available in that step only.

Refering back to the payload from the output of the Get query results step, we will assign the sales person's name to the variable name and their current sales figure to the variable sales. We will also want the array from the previous step that we will be adding to every loop, so we will set that to the variable arr.

variables

We can now refer to these variables in the script and will write some simple Javascript to return the array with the string combined using interpolation added.

// You can reference the input variables using input.NAME
exports.step = function(input) {
input.arr.push(`${input.name}: ${input.sales}`);
return input.arr;
};

Remembering that:

input.name refers to the sales person
input.sales refers to their current sales figure
input.arr refers to the array that contains the formatted strings that will be used to send the message

We then set this array into our sales array, which once it has finished looping, will contain strings of our sales people and their current sales figures. If we get them in the first step after the loop, we can see what the payload looks like:

sales

2 - Format the data to be readable

We're almost there, but we still need further formatting so the message is displayed properly in Slack. We will use another Script connector to further format the array to make it readable. So instead of an array of strings, we will join each element in the array into one string, where each element is separated by a line break.

Again, we will create a variable to be used in the script, which will be the sales array:

sales-variable

Then we use it in the following Javascript:

// You can reference the input variables using input.NAME
exports.step = function(input) {
return input.sales.join('\n');
};

For which the output is:

readable

Now we can be use this to compose our message in Slack! In our case, we add it to the Text field in Attachments using the Send message operation, which displays our message like this:

message

Was this article helpful?
Yes
No