Change the name of a field from Google Sheet

Queries and Resources
1

I have a Google Sheet query but I'd like to change the names of the fields/columns.

The spreadsheet data comes from a web-based Form (JotForm) and so the columns are important to tie-up with that. And one of them has a question mark '?' which I think might be causing trouble as a field-name.

I thought I might be able to use a transformer to do this but I don't know how to do this in Javascript?

Any help greatly appreciated.

2

Hey @jclutterbuck,

do you need to "change" already existing headers in a sheet? I'm assuming that any data that came in up until now will be tied to these headers.

Why not create a "new" sheet when a new jotform is streaming data?

You can use the Google Sheet query's "append" action type and pass in your new headers. Each subsequent call to "append" will not insert new headers, just append the data.

Re: question marks in your question titles that need to be converted to headers. You'll need to write some sort of code to execute that logic. Could you post the headers as you get them currently and what format you would like as the output?

Cheerios,
mj

3

Thanks @minijohn,

I have made some progress. I use a transformer to add a key to the data returned from Google Sheets that is a copy of the field that I need.

for (let j = 0; j<data.length; j++){
  data[j].WhatDate = data[j]['What date are you not available?'];
}
return data;

However, what I am trying to do is to work out which entries in the data have the same date as a row in the listView where this is called from.

This is usually done using the special [i] variable. In my app I have the query GetObjectsOnAccount which supplies the data to the listView and so I use GetObjectsOnAccount.data.EventDate[i] to get the current EventDate of the current row in the listView. If I hover over {{GetObjectsOnAccount.data.EventDate}} I get an array of dates and if I hover over {{GetObjectsOnAccount.data.EventDate[i]}} I get undefined - I guess because [i] is only available at the run time?

However, when I run the query there is no value associated with {{GetObjectsOnAccount.data.EventDate[i]}} So when i try to manipulate my data to find the difference between it and the data I get "NaN".

E.g. if I add a key 'EventDate2':data[j].EventDate2 = {{GetObjectsOnAccount.data.EventDate[i]}}; I get an empty "column"!

I am wondering if [i] isn't in the scope of this query? It is called from a dropdown select component on the listView which calls one query based on two other queries?

Any clues? Thanks

4

@jclutterbuck

Hey there :wave:

Just to clarify, are you trying to access a specific component in this list view when you use i? (For example: Are you trying to grab the most recently changed select component)?

5

Thanks for your replay @lauren.gus.

I'm trying to refer to a date associated with the record in the listView from where the dropdown component was clicked. The data, as per the examples in the help docs, comes from the query that provides the data for the list view.

So the Row Keys is {{GetObjectsOnAccount.data}}

the listView is listView1

The Dropdown component on listView1 is clicked and the query runs on the Focus event.

Then to get the date for that record I use {{GetObjectsOnAccount.data.EventDate[i]}}.

This is the data I get when I add a column called "EventDate2":

retool i
retool i1920×909 122 KB

You can see the column is blank and the Difference column says "NaN".

Any ideas?

Thanks

Jeremy

6

Hi @jclutterbuck, My response to your other post should help here!

1 Like
7

Thank you.

I have a new issue with [i], perhaps you can help?

New mapping Dropdown from a listView component

Jeremy