When using Google Sheets as a data source in Klipfolio, often times we come across issues with their static references offsetting columns, when a minimum change is made in the base data structure.

For example:

We created a new data source in Klipfolio using a shared Google Sheet and attempted to appropriately define each column and input data.

Once the the data was entered, we created a Klip to showcase the information in the spreadsheet by using static references (i.e. column A:A) and basic functions.

This proved to be problematic…

A few days later, members of our team decided to make changes to the spreadsheet by altering data and adding new columns. Since the data in the Klip was sourced from the structure in the original Google Sheet, the alterations made to the spreadsheet caused the Klip to break. As expected, this resulted in the need for frustrating rework from our team.

This problem is one that can certainly cause headaches because with even the slightest change, any Klips that are sourced from Google Sheets will stop working and require reprogramming.

As a result, I worked towards finding a solution.

My Findings

1. Improved planning and analysis of data structures
   I’m a fan of investing time into developing a solid plan.This means identifying and analyzing details and possible outcomes; in this case, thinking about what potential additions will be made to the data set in order to leave room for them instead of making changes that will break the Klip.
   
   
2. Make column additions to the end of a file or in a separate spreadsheet
   By making additions to the file in this manner, you eliminate the risk of breaking the Klip because you are simply adding to the data source in lieu of making direct changes.

The Use of Klipfolio Pointers

What is a pointer in computer science?

A pointer is defined as a programming language object, whose value refers to (or "points to") another value stored elsewhere in the computer memory, using its memory address. A pointer references a location in memory, and obtaining the value stored at that location is known as dereferencing the pointer.

Pointers and Klipfolio

When building a Klip in the Klipfolio app using a spreadsheet as our data source, if we want to get the data of a column to show up as the first column of a Klipfolio table, we usually:

• Select the column of the Klipfolio table
• Click the Data tab
• Input a specific formula and click on the letter of column that is needed from the bottom cage, as seen below.

With these actions, a static reference, highlighted in blue, automatically appears in the formula bar. The column in the Klipfolio table, in-turn is filled in with the selected data. Overall, this is a straightforward process due to the user-friendly Klipfolio interface.

Looking at this process from a more analytical point of view, what happens is that when certain events are detected, in this instance, clicking on a column:

• Referential code, highlighted in blue, is inputted automatically in the formula bar
• The referential code creates the column object
• The column object is filled in with the text, @Hoja,A:A

The code in blue, @Hoja,A:A, by itself is already a static pointer. By copy and pasting the blue text into a notepad, it appears to have more more text than we normally see, as seen below.

This is because a Klipfolio pointer is formed using 3 unique parts, broken down as follows:

b4feb001ece1d803c6f468cb83fd319a@Hoja1,A:A;

     -      ID of the datasource             

• @ and ;  

• Text  or path of the sheet

The DATASOURCE formula that exists within Klipfolio, allows us to create a dynamic pointer to change the ID of the data source and the path of the sheet altered to the specifications we need.

DATASOURCE(datasource_id,pointer)

Returns the data referenced by pointer in datasource_id.

Parameters

datasource_id: A data source identifier, typed as a literal string.

pointer: A reference to a field in the data source, for example, a column, cell or XPath, typed as a literal string.

And so, if we compare the DATASOURCE formula with a static pointer, we find that both have the exact same components, but with a different format.

b4feb001ece1d803c6f468cb83fd319a@Hoja1,A:A;

DATASOURCE("b4feb001ece1d803c6f468cb83fd319a","Hoja1,A:A")

Note: By using the DATASOURCE formula, Klipfolio does not recognize that datasource directly displayed at the bottom, so on importing the Klip to a client, the datasource will not be recognized. The step that must be taken is to import the datasource outside of the Klip. You will need to use a global variable to change the new ID's value.

This formula is not recommended unless you have a truly dynamic objective case. For example, if you have two brands with the same structured file and different information, you only need to change the ID of the datasource to load the corresponding data.

Optimal option although complex to understand

 3.- Use a set of formulas to fix the columns from their header.

Linking back to our goal of attaching the columns of a Google Drive spreadsheet from their column title, what we are going to do is to play around with the last part of the path of the sheet in the pointer. We do so in order for the path to dynamically rotate the value of the column that we want to select, while we look for the title in parallel, by doing a match up between the positions of each arrangement.

A:A  or B:B  or C:C or  D:D or E:E or …

Example of data source visualization

An example of data course visualization is having a spreadsheet with several unique titles in line 1:1 and more data in the following lines like the next chart.

On making the 1:1 reference in Klipfolio, we will obtain a vertical arrangement with all of the unique titles. In turn, we can observe that each one has a position in the array and if we create a second array with the names of the columns of a spreadsheet, we could associate each title with each column.

Steps to fix the columns from their header

Objective: Obtain the information of a column from the unique header of the column using Klipfolio formulas.

Requirements:

• Have a datasource with formatted as a spreadsheet that has unique titles in the same row of the selected sheet.
• Have an array of the column format series with around 100 values.
  
  

I recommend to creating a new Excel file and fill the column A with the series of letter format columns  A:A or B:B or C:C or D:D or E:E or … Link Download Columns file

Structure of the formula:

SLICE(

DATASOURCE(datasource_id_Spreadsheet,

CONCAT(“Sheet,”,

SELECT(&Columns, DATASOURCE(datasource_id_Spreadsheet,”Sheet,1:1”)=&title))))

Where,

datasource_id_Spreadsheet  is the ID of the spreadsheet datasource you want to use.

Sheet, is the name of the sheet to use in the spreadsheet datasource with a comma at the end.
Note: Sometimes you can leave this blank if the file has just one sheet with no comma.

&Columns  The array of the column format series {“A:A”,”B:B”,”C:C”,”D:D”,”E:E”,...}

Sheet,1:1 is the path of the row where you have the unique headers in your spreadsheet datasource.
Note: Check the rows where your headers are to adapt 1:1.

&title is the header of the column you want to fix, you can use reference or type as a string like “Title1”

I know this is a bit complex, so I’ll explain it as if we were debugging each section in order to to compare the expected result:

&Columns

The result of this must be the array of columns

A:A,B:B,C:C,D:D,...

---------------------------------------------------------------------------------------------------------

DATASOURCE(datasource_id_Spreadsheet,”Sheet,1:1”)

This will get the array of headers      

Title1,Title2,Title3,Title4,...

---------------------------------------------------------------------------------------------------------

SELECT(&Columns, DATASOURCE(datasource_id_Spreadsheet,”Sheet,1:1”)=&title)

This will get the name of the column that we find in the same position row as the header searched for

if &title=”Title3”, entonces nos traería de resultado “C:C” el cual ya es dinámico dependiendo de la referencia de &title.

---------------------------------------------------------------------------------------------------------------

CONCAT(“Sheet,”,SELECT(&Columns, DATASOURCE(datasource_id_Spreadsheet,”Sheet,1:1”)=&title))

This should return the name of the sheet and the column we looked for. Following the example above, the result is “Sheet,C:C”.

--------------------------------------------------------------------------------------------------------------

DATASOURCE(datasource_id_Spreadsheet,

CONCAT(“Sheet,”,SELECT(&Columns, DATASOURCE(datasource_id_Spreadsheet,”Sheet,1:1”)=&title)))

The dynamic pointer that uses the DATASOURCE formula.

DATASOURCE(“245ef21b0d4f62d4ee2c95df349ce889”,”Hoja1,C:C”)

Result:  (“Title3”,”data5”,”data6”)

SLICE(DATASOURCE(datasource_id_Spreadsheet,

CONCAT(“Hoja1,”,SELECT(&Columnas, DATASOURCE(datasource_id_Spreadsheet,”Hoja1,1:1”)=&title))))

The last part is a SLICE formula to remove the header of our dynamic data results;:  (”data5”,”data6”)

Quick Walk Through

Follow these steps to review the formula:

1. Create/upload a file as a datasource named "Columns" that contains 115 text records that represent the order of the columns of a spreadsheet, in the first column. The series should look something like this A: A, B: B, C: C, D: D, ... AA: AA, AB: AB, ... etc.
   
   Excel link to download:

https://app.klipfolio.com/datasources/view/b4feb001ece1d803c6f468cb83fd319a

2. In the Build Klip view, create a label and add 5 hidden data with the following values:

• “Columns” - With the value of the ID of the datasource created in Step 1
• “ID_Drive” - Must have the ID of your spreadsheet datasource to use.
• “Sheet_Drive”  - Type the name of the sheet that has headers.
• “Row_Drive” -Type the row where your headers are “1:1”.
• “Title1” -Type the name of the header you want to fix.

In my example, the values of each data component are:

!Data: Columns =   DATASOURCE("b4feb001ece1d803c6f468cb83fd319a","Hoja1,A:A")

!Data: ID_Drive  = "245ef21b0d4f62d4ee2c95df349ce889"

!Data: Sheet_Drive  = "Hoja1,"

!Data: Row_Drive  = "1:1"

!Data: Title1 = “Fecha”

The formula should then look like this:

The next step is to repeat the formula and to continue changing the title, or modifying the sheet and line of headers according to what specifications are needed.

This solution has worked very well for the development of fast Klips that will be used for a long period of time. These Klips are used when I’ve had no way to connect to an API to obtain the data and when my team chooses to use a shared Google Drive, manually feeding new data each week or month, as a budget sheets, sales goals or results of advertising campaigns using many different media platforms. Sometimes this technique is a quicker way to present results with little time available than to connect with each API. As you may already know, connecting to APIs requires a heavy investment of time.

For these reasons, I hope you find my solutions helpful!

This article is based in a response received by Josh Cohen-Collier when looking for ways to solve this problem. https://support.klipfolio.com/hc/en-us/community/posts/115001898588-How-to-set-a-permanent-column-from-google-drive

Additional note:
The formula of DATASOURCE has a huge scope if you nest this formula several times through intersections of dynamic titles. Creating another column of an array of rows ("1: 1", "2: 2", "3: 3", "4: 4", ...) will assist in the organization and efficiency of your data and Klip.