Google Sheets - Insert Columns

Updated 1 month ago by Karan

Introduction

Byteline provides a Google Sheets - Insert Columns node to configure the Google Sheets column mapping using Byteline expressions.

Use this task to export data to Google Sheets by mapping the Sheets columns to different expressions. In contrast, you can use the more straightforward task of "Google Sheets - Insert All Values" to send all values from a single expression to the Google sheet.

Create Google Sheet - Insert Columns node

For this document, we are assuming that a flow has been initiated with the Scheduler node and followed by Reddit - Get Subreddit Posts node. But you can use a Google Sheets - Insert Columns node with any trigger. For more detail, you can check How to create your first flow design.

Step 1: Select the Google Sheets - Update Values node from the select node window.

Step 2: Click on the edit button of the Google sheet update node.

Configure the Node

Sign in to Google Sheets

Click on the sign-in button on the configuration window of the update/insert Google Spreadsheet values node.

How to Copy Spreadsheet Id

Step 1: Open your Google spreadsheet and copy the spreadsheet Id from Google Sheet URL.

Step 2: Paste the copied Id in the Spreadsheet Id field.

Step 3: Enter the sheet name in the field below.

Users can check the Auto generate headers to generate the headers automatically based on the JSON field IDs. This action can only be performed when the spreadsheet is empty.

Step 4: Tick the Clear Value from Spreadsheet checkbox to clear the spreadsheet before inserting the newly fetched data.

Loop Over

You can iterate over an array using for loop over. Instead of processing element by element, you can iterate the index by using loop over starting from 0 to the length of the array and access elements at each index.

Step 1: Click on the loop over checkbox to enable the loop over.

Step 2: Select an array from the Loop over dropdown menu to apply a loop over the array.

Configure Spreadsheets Columns

Step 1: Enter the column name in the header field.

Step 2: Click on the selector tool to pick the column path.

The column path is automatically fetched in the value field.

Optional: To Replace Function

Step 1: Click on the selector tool for the function you want to replace. This function can be used if you want to replace part of the expression value with something else.

Step 2: Click on the function button to view all the functions.

Step 3: Click on the replace button to replace the existing function.

Step 4: Enter the expression you want to replace in the search for field.

Step 5: Enter the replacement expression in the replacement field.

Step 6: Select either the Yes or No option from the is regex dropdown menu to confirm that the replacement is of a regular expression.

Step 7: Click on the save button to save the function replacement.

You will observe the function is replaced in the value field as shown below.

To Add Row

Click on the add button to add new header-value pair in the node configuration window.

To Delete Row

Click on the delete button to remove the row, if not required.

Click on the save button to save the node configuration.

Here you can insert a new row or update an existing row.

Once you have saved the configuration, the indicator over the top-right corner of the node will turn green. The green indicator shows that the Google Sheets - Update Values node has been successfully configured to your flow.

Using Google Spreadsheet functions

If you need to use Spreadsheet formulas in your Google Sheet and use the Byteline's Google Sheets - Insert Columns task, you can add those formulas to the Byteline task. It's even more important to do that with Clear values from Spreadsheet option, as otherwise, your manually added functions will be overwritten on the next run. You can use the Spreadsheet functions as the values in the Byteline task's field mapper used to configure the value expression.

Below is an example function that splits comma-delimited image URLs into different columns. The formula uses dynamic values for the rows and columns. This example discusses the split function, but you can use the below methodology for any Spreadsheet function.

Formula used: =SPLIT(indirect(address(row(),column()-1)), ",")

Below is the explanation for various parts of the function

SPLIT: This function takes a cell reference and a delimiter and splits the values. It's generally written as SPLIT(B1, ",")

indirect: Indirect function converts text value to a cell reference. 

address: Given a number-based row and column, this function returns the text representation of the cell reference. For example: "$B$1"

row: This function is used to find the current row

column: The function is used to find the current column.

In brief, this formula takes the comma-delimited images from the column left to it and performs a comma-delimited split.


How did we do?