greetings
I’ve made a feedback comment section in my app similar to the survey app project on thinkable. I have linked it to a google sheets data source. I set up several columns to automatically take in the device date and time to know when it was submitted. Since there is no block to take the entire date and time, I added each one individually then used the DATE & TIME functions to merge them. However, if I copy and paste the formula down to new rows so that the date and time would automatically be connected, the new row starts below, summing that the previous rows’ functions mean there are not empty. Interestingly enough when Inserted a check box on the end it does automatically paste itself to the next row
does anyone know of a workaround for this?
I’m not really understanding the problem.
-
Is it a problem only in Google Sheets? Or a problem when a new row is created?
-
Where did the #NUM! and 12:00:00 AM values come from? Did Thunkable generate those or did you create those in Google Sheets?
-
Is it creating the new row correctly? What’s missing/incorrect?
-
Can you give examples from your actual sheet (e.g. “It should show 3:42:55 PM in column G but it is blank”)?
sorry for not being more specific
1 this seems to be a problem on thunkable’s end
2 the #NUM! and 12:00:00 AM are from the DATE & TIME functions I added in the row above the two examples I entered by using the live app testing on my mobile phone
3 the example would be that if there is nothing in the row but the DATE and TIME functions in there thunkable consider’s that to not be an empty row and will add the information on the row below them like in the screenshot
what I don’t understand is that when I used the insert checkbox on google sheets thunkable doesn’t view that as a filled cell and adds the information in that row
Okay, I see what you’re saying. Thunkable is adding a row after an empty row that only contains formulas.
When I try to create a row when I already have a formula in a cell in the new blank row, Thunkable replaces the formula value with a blank cell. It doesn’t skip a row like in your example. But it does overwrite the formula contents.
One way to get around this is to write the formula back to the cell. You can put a Google Sheets formula such as =LEFT(A30,5) in the create row block using a text block and it will update the cell to that value and work as a valid formula:
Another option is to use Airtable which doesn’t store formula values in cells but rather in column headers. That way, you never have to worry about overwriting the formula.
Will the formula update as you go down to each row?
If it starts as =left(a30,5) will it continue on as a31 32 and so on
No, but you can code that in Thunkable. Just use a variable that starts at 1 and each time you create a row, change the value by 1. Then use the Join block from the Text drawer to concatenate that value with the row letter.
Use this block and add 1 to its result.
This will always point to the newly created row.
Even better! 
where exactly would i need to put this block in the create row block?
In place of the row number. So if your formula was “=left(a30,5)” then you would do this:
Join “=left(a” + number of rows in table + “,5)”
And that would give you cell values like this:
=left(a1,5)
=left(a2,5)
=left(a3,5)
…
Since I am using the DATE function in order to merge 3 cells in 3 columns together to form a standard date would that be similar to the example above?
i attempted to use the join block to write out the function as pictured above
but the date that came up was not the same as the information from the other columns
does anyone know what I did wrong?
Well, you’ve created a formula like this:
=DATE(30,30,30)
I doubt that’s going to work.
What is the Google Sheets date formula you’re using that does work?
the function formula i was using was
=Date(a2b2,c2)
I was finally able to figure out how to make it work. the code block below shows how to use the join block to write the function out. the missing piece was to use a math block to add 1 to the number of rows block
the first entry reads as an error because this configuration will make it start at row one but the next entry starts working fine afterwords.
This method should be able to be used to make any spreadsheet function work directly from thunkable
That makes sense because the number of rows in an empty spreadsheet is zero. That value doesn’t get updated until after a row is created. So when you create the first row, the value is zero and you need to add one to it. Then, if you create a second row, the value is one and you need to add one to it, etc.
Yes
I was able to use this way to make the TIME function work as well
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.