Spreadsheet component. Example. Google Sheets

Like so


I am trying to identify why my app will not write to my Google sheets file. I have set everything up in Google Cloud. Here are my blocks and the error message I receive on my phone when using the emulator.

Set CredentialsJson is expecting a file, you are providing a code.

Set Spreadsheet ID is expecting a Spreadsheet ID, you are providing a file path

I made the corresponding changes but I am still getting an error. Attached are my blocks and the error that I am receiving.

Have you tried setting the credentials file in the Spreadsheet component designer properties ? (and the Spreadsheet ID for that matter)

If you do try this, remove or disable the setting blocks in your blocks editor.


I ran a test, using my own credentials and spreadsheet. This works OK



more work to do at your end....

There also seems to be some kind of bug with the join block

This works


This works


but this fails (which is what you are doing in your blocks)



error message:
WriteColumn IOException: 400 Bad Request PUT https://sheets.googleapis.com/v4/spreadsheets/1IdpGILd7Nii111111o5la2xbDN1Cok/values/Sheet74!E:E?valueInputOption=USER_ENTERED { "code" : 400, "errors" : [ { "domain" : "global", "message" : "Invalid values[0][0]: list_value {\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n}\n", "reason" : "badRequest" } ], "message" : "Invalid values[0][0]: list_value {\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n values {\n struct_value {\n }\n }\n}\n", "status" : "INVALID_ARGUMENT" }

This is a workaround, by first setting the joined text to a label, then the label text to the make list block in the WriteColumn method (the label does not have to be visible). Just setting the join output to a local variable doesn't work.


Thank you that worked. How would I list each individual component on the list in its own cell all on the same row?

You want to write a row, instead of a column?


If you want to append a row, use the .AddRow method.

That's interesting. I'm not sure if that's a bug with Spreadsheet or something involving just the join block that happens to rarely affect anything.

Very interesting that a local variable also doesn't work.

It is an odd one, the join output is obviously not being transmogrified as expected :wink:

The reason this happens is due to how Kawa handles strings. It has its own internal type called FString which acts like a rope, and so when you join strings together you get an FString reference that points to the constituent parts. This is to save memory rather than introduce a new String object that takes up the same space as the originals.

Now when we come to the Spreadsheet component, the runtime does not sanitize all of the internal types of the list when passing it the component since it doesn't know (and actually, in the Spreadsheet since numbers should also be acceptable, can't know) what the internal types should be. The component therefore should be sanitizing the list contents based on its needs. However, it passes the values through to the Google Sheets API, which does not know what an FString is or how to use it.

On the other hand, when a FString is passed to a component property/method that expects a String as its argument, Kawa will convert the FString to a String before making the call, so by assigning the result to a Label's Text property (or any other property typed as String) the FString is converted to String and then when it is passed through to the Spreadsheet the Sheets API can consume it.

Variables won't work because they are not typed, so no type coercion happens.

Is there any way to manage this in the Spreadsheet component? I believe only numbers and strings can be passed through the Sheets API, and possibly everything is effectively a string with number behavior handled by the Google Sheet before passing values to/from the API.

Yes, I could probably put something together real quick.

Edit: PR is now up:

Thank you! I was able to make the recommended changes.

Currently I have the data going to particular cells in the spreadsheet. Is there a capability to populate the next row if another user works with the app? Essentially I am trying to populate the spreadsheet with different users inputs from the app.

You will need to show/explain exactly what you mean.

Most things are possible.....

The Add Row block is perfect for that, since it does not need to told by the app where to put the new row.

It just adds a new row to the bottom of the sheet.

There should be no problem with overlapping Add Row requests by different users, since I would expect Google Sheets would take care of that.

Here are my blocks and a snapshot the google spreadsheet. What I am trying to do is essentially keep the data in the first row and then from any other entry it populates the next collection of cells.

Looks like you need to work back and start again.

Create a list that contains all the items you need in a row

AppendRow that list

much more straightforward.