I'm trying to do something that I thought wou...

AppSheet AppSheet Q&A
1

I’m trying to do something that I thought would be simple but am having a lot of trouble with.

If someone give me a simple “You can” or “You can’t do that” in AppSheet, I’d really appreciate it.

In my app, a “Session” number gets incremented after a series of jobs have been completed.

In the “Session” table, there is only one cell for the number so a date stamp would only show the date of the latest change.

I would like to make an archive on a separate sheet in which the date and time are written in a new record every time the “Session” number is changed.

Can I do that in the background with actions?

If so, can you direct me to a sample app or post, etc. that shows how to do it?

P.S. This inquiry is about the same issue I tried to describe here:

https://plus.google.com/105035465983967629689/posts/9X6PRczC3mQ

2

@Aleksi_Alkio As always, I appreciate your help.

In response the following question

“You have one data table and every time when you add a new record, you would like to log the Datetime and session number in another table?”

my short answer would be “no.”

There are a few differences.

One is that the log would be in response to a change in a value, not the addition of a record.

Conversely, when the value is changed, I would like a new record to be added to the “log” sheet to record the date, time, and value of the change.

If you go to 17:30 of the video @Mike_Adler introduced, you can see an example of the kind of task I’m trying to accomplish.

In regard to Query sheet formulas, I’m unfamiliar with them.

I’m not sure if they will help in this case but I’m interested in expanding my repertoire of gsheet skills so I’d like to look into it.

3

Are you saying this… You have one data table and every time when you add a new record, you would like to log the Datetime and session number in another table?

4

Before we can do this with the in-built action, we need to have the option to add records automatically which is not yet possible. That’s why we need scripting or like Zapier for doing that.

5

Got it!

That’s what I thought.

The option to add records automatically will be welcome when and if it comes.

6

@Kirk_Masden - as a side note, gsheet queries are extremely valuable to learn - I use them all the time.

Take look… will be worth your time! Google Sheets Query function: Learn the most powerful function in Sheets benlcollins.com

7

I believe the last Office Hours session showed an example app that was using Zapier to post each activity as a history record in second google sheet.

When asked why they used Zapier, i think the response was that was the easy way to log the responses.

Maybe that example might help?

8

You can check out the video here: youtube.com - Attendance App | AppSheet Office Hours | July 2018 | AppSheet

Attendance App | AppSheet Office Hours | July 2018 | AppSheet

9

If the answer for my question is yes, then one solution could be a simple Query sheet formula in your gSheet.

10

@Mike_Adler I’m looking at the video now. The addition of records we see at 17:30 of the video is just what I was hoping to do.

I’m going to study this some more but I’d like to be able to my share it with students as a self-contained one, so I’m not sure if I would like it to rely on something external like Zapier.

Still, I’m glad to learn about Zapier – it seems to be doing something like what I have been doing with a script on another Gsheet I have.

Because the AppSheet pros used Zapier to accomplish this, I think this provides a clear answer to my yes-or-no question about whether or not what I wanted to do in AppSheet is possible without an extension like Zapier:

that is, “no.”

On the other hand, I’ve decided to use a work around that I can implement with available AppSheet functions.

In that work around, I start with 1000 records (enough to last several years in this case) and use counta in my Gsheet to determine where the next log entry should be written.

I use several “Execute and action on a set of rows” actions to write the data in the appropriate cells on the Gsheet.

It’s not as elegant as Zapier seems to be, and the log space will eventually run out because AppSheet forces my to make it finite, but it’s self-contained so I think I’ll go with that for now.

Thanks @Mike_Adler and @Aleksi_Alkio for your help!

11

P.S.

Zapier is cool but I think the AppSheet platform will become more valuable if a simple-to-implement “history” function can be added to the AppSheet action arsenal.

12

@Kirk_Masden - glad the example helped.

I also wish appsheet could allow history record capture natively.

Using Zapier is a workaround at best and adds complexity.

But, for now, may be the only choice.

13

@Mike_Adler As I write this, I’m in the middle of writing the native AppSheet work around (that begins with a finite number of records).

So, I would say that Zapier is one of at least two work around choices but it will be nice when a simple native solution comes along.