[OBSOLETE] Simple Event Logger

[TN_Oldman (Don)]

Just updated this to latest, and it appears to still be logging into my sheet

Thank you very much for this. I yet have had time to set up and charts and such. Lucky to even get it updated.

[TN_Oldman (Don)]

Is there a obvious way to copy the new incoming data from the main data set to a different sheet for use in creating tables, charts ect…

I would prefer to not use the sheet that the app is logging data to , but if you could automatically copy the data to different sheets then you could manipulate the data as you wanted to create the charts and graphs you want to see.

Plus I am having issues trying to figure out how to make that pivot table and charts update when data is added? Do you have to create it every time you would like to chart by selecting everything again?

I’ll keep looking / self educating. Was just hoping, like everyone does, it was easy and something someone could point me in the right direction quicker than the self education route.

Thanks in advance

[krlaframboise (Kevin)]

@TN_Oldman

Is there a obvious way to copy the new incoming data from the main data set to a different sheet for use in creating tables, charts ect…

Go to File > Make a Copy

It will prompt for a new name and then open in a new tap.

@TN_Oldman

Plus I am having issues trying to figure out how to make that pivot table and charts update when data is added? Do you have to create it every time you would like to chart by selecting everything again?

You don’t have to re-create it. If you click in the pivot table you should see the “Report Editor” window which has a link at the top for “Edit Range”. If you update that to the last row in the main sheet it should update.

If you’re using that date column with the formula, you might have to add the formula to all the new cells, but that’s really easy:

1. Click the last cell with the formula.
2. Hold the ctrl key and press c
3. Press the down arrow key
4. Hold the ctrl key and the shift key and then press the down arrow
5. Hold the ctrl key and press v

BTW: The “Explore” feature, which you can open by pressing “ctrl + Shift + x”, generates some reports and pivot tables automatically.

I still plan on making this process easier, but I’m juggling a few different projects at the moment.

[TN_Oldman (Don)]

@krlaframboise

Events: This will show another field that allows you to specify the maximum number of events to log in a single Sheet. When the new events being logged will exceed that number or the sheet is out of space it will archive the data.

So say I set this to 50,000 I could then lock my pivot tables to a set range of Sheet1!A1:E50000 (or something like that). Then it will archive the data, wipe the base sheet, then start logging again before the 50,000 limit.

This would allow for my Pivot tables and graphs to remain constant (set range) without any editing. (In theory)

So If I figured out what a weeks worth of data points was (roughly) I could (theoretically) generate a clean sheet, graph, pivot table. Once a week?

Or is my thinking way off?

Thanks for the other answers. I have that working. Just trying to figure out a way to not have to generate the graph , pivot table each time and having to include the NEW data points into it. Kinda a dynamic thing, if you get my idea.

@krlaframboise

I still plan on making this process easier, but I’m juggling a few different projects at the moment.

I am not asking you to work on this. I appreciate what you have already done. Just had some free time and was trying to learn how to manipulate the data for ease of viewing.

I just need to spend some time in Google World and learn how to move data, apply filters, and such automatically to manipulate the data around, (share to people) so it’s something my wife can look at.

Posting to the forums is sometimes just me thinking out load and bouncing ideas off others more knowledgeable than me. To say Ahhhh bad idea, or yeah that’s possible!!

[krlaframboise (Kevin)]

@TN_Oldman

I could then lock my pivot tables to a set range of Sheet1!A1:E50000 (

In theory that should work, but I’m not sure if the empty cells will skew your numbers.

[TN_Oldman (Don)]

So your saying it’s possible? Hahahahaha

Guess well find out. Time to make a copy, do some tinkering and see what happens!

Thanks for the quick reply.

[TN_Oldman (Don)]

Okay had a few minutes slack time today and figured out these 3 additional ways to move data in Google sheets from your main sheet that is getting the data written by this great app

This filter will copy any data entered on your master sheet to another sheet. so essentially making an auto backup you can play with and not worry about messing up your data coming from the app.

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/!!@@##$$%%vqSb6xQ2L5qmt77Gz9A-lN-hlWKASlh3fQ/edit?usp=sharing”,“Sheet1!A:E”)

You will need to change the hook to your sheet by editing url that points to it. You can grab that from the browser. Each sheet has a unique string of characters that identifies it. Which follows the spreadsheets/d/???-??? In your url.

This next way will grab the data from your main sheet based on the text value between the quotes. Say for example “motion” in column C. It takes a while to gather the data so might throw an error, if you got it right it will after a little bit populate your new sheet with what it found.

=Filter(IMPORTRANGE(ʺhttps://docs.google.com/spreadsheets/d/@@##??OCvvcLoQ2L5qmt77Gz9A-lN-hlWKASlh3fQ/edit?usp=sharingʺ,ʺSheet1!A:Eʺ),(IMPORTRANGE(ʺhttps://docs.google.com/spreadsheets/d/@@##$$%%lN-hlWKASlh3fQʺ,ʺSheet1!C1:Cʺ) =ʺmotionʺ))

The last one will filter the data from your main sheet based on the number values reported in Column D. Again it could take a while to gather the data so might throw an error, if you got it right it will after a little bit populate your new sheet with what it found.

=Filter(IMPORTRANGE(ʺhttps://docs.google.com/spreadsheets/d/$$$%%###@@@???cLoQ2L5qmt77Gz9A-lN-hlWKASlh3fQ/edit?usp=sharingʺ,ʺSheet1!A:Eʺ),(IMPORTRANGE(ʺhttps://docs.google.com/spreadsheets/d/???###$$$$%%%%@@@@LoQ2L5qmt77Gz9A-lN-hlWKASlh3fQʺ,ʺSheet1!D1:Dʺ) <90))

Remember if your working with a good sized original sheet Google WILL get LAGGY. You might have to wait a little bit to do edits and adjustments while it is trying to crunch your formula. It also can throw a range error (or something like that) about the cells or something while it’s filtering the data.

Also remember you have to use the url to YOUR sheet in the filter. I have messed the example up.

Also remember the sheet your filtering the data from MUST be shared, this shouldn’t be an issue if your filtering the data from your master sheet the app is using.

These get entered into the first cell of the sheet, in case that wasn’t obvious.

I’m sure there is other ways to manipulate the filter. By doing this you could control the data on a sheet, generate your pivot table and chart off only that data. I haven’t gotten that far. Maybe tomorrow I’ll have time to play a little more. should probably get back to work???

[chewmull (Matt)]

I just noticed my event logging just stopped a couple days ago. The event log shows the following:

7:45:23 AM: warn Google Sheets Web App failed to log events between 02/20/2017 16:43:20 and 02/26/2017 07:45:11
7:45:23 AM: debug Google Sheets Web App Reported: Cannot find function getFullYear in object

I am on the latest version of the script and app and the environment has not changed. Any thoughts?

[Mario_Fuchs]

@chewmull

I just noticed my event logging just stopped a couple days ago.

Google docs had connection problems and threw errors on my Zapier and IFTT connections. Re-authenticate fixed the problem. This could be related to your problem.

[joelw135 (Joel W)]

I have been trying to create a chart, but I keep getting errors, I have tried changing the time:Dat to just date, No luck here is a screen shot. Would someone please guide me to a fix? Thanks.

[TN_Oldman (Don)]

Are you asking how to change the date, time value to just date? So you can use that date (day) as a value in your pivot table?

For example from 2/26/2017 9:16:20. To just 2/26/2017

[TN_Oldman (Don)]

Did you try changing the column format?

Select the whole column by clicking on A
Then in the menu up top select format
On the next pop out select Date instead of Date time.

See below picture:

[krlaframboise (Kevin)]

@chewmull

I am on the latest version of the script and app and the environment has not changed. Any thoughts?

Open the SmartApp, tap the About Simple Event Logger link at the bottom, and double check the versions shown on that screen.

[krlaframboise (Kevin)]

@TN_Oldman

Did you try changing the column format?

Select the whole column by clicking on AThen in the menu up top select formatOn the next pop out select Date instead of Date time.

That changes the way the data is displayed, but doesn’t change the actual values so it won’t have any effect on reporting.

[krlaframboise (Kevin)]

@joelw135

I have been trying to create a chart, but I keep getting errors, I have tried changing the time:Dat to just date, No luck here is a screen shot. Would someone please guide me to a fix? Thanks.

You didn’t create that extra column like we previously discussed:

[OBSOLETE] Simple Event Logger

Each column should have a header so it looks like you’re not starting at the top of the page. You should type “Date” into Row 1, Column F. Type =int(a2) into row 2 of column F, copy that cell, select all the cells in column f below it and paste. I’ll release a new version tonight that has a function that you can use to automatically perform these steps or if I have time I’ll add a setting to the SmartApp so that it can optionally populate that column automatically during the logging process.

[TN_Oldman (Don)]

Well, yeah that makes sense.

Then I guess he needs to refer to your previous discussion around post 73 or something like that.

I guess that’s what he’s looking for again. Also wouldn’t he need to create a pivot table so that the reduced date is in the first column?

[chewmull (Matt)]

SmartApp 01.02.01
Script 01.02.01

Still not working. Additional event log error:

10:57:47 AM: error java.util.concurrent.TimeoutException: Execution time exceeded 20 app execution seconds: 159154213789646 @ line 661

Thoughts on things to try next?
Thanks

[krlaframboise (Kevin)]

@chewmull

Thoughts on things to try next?

When you were getting the other error, were you on the correct version of script and smartapp?

The error you’re seeing now is caused by it trying to send 6 days worth of events which it’s unable to do within 20 seconds.

I overlooked this potential problem so at the moment there’s no way to fix it, but I’ll release a new version later today. The new version will allow it to continue from where it left off so you won’t lose any data for the 6 days it skipped.

[chewmull (Matt)]

Yes, I haven’t updated either since the day the current one was released. That’s good news on an updated, I’ll try it out as soon as you release it. Many thanks!

[krlaframboise (Kevin)]

@chewmull

debug Google Sheets Web App Reported: Cannot find function getFullYear in object

I looked into this while making the other change and you must have done something to the first column because when it tried to archive the data it came across a cell that wasn’t a date.

The new version will have a catch for that which will use “undetermined” instead of throwing an error when that happens.