I have a table that records data for various dates. I’m using a slice which shows only the current month’s data from the table. I want to save (an e-mail probably, I learnt saving locally can be achieved by using an API) all the data associated with that particular view. Can that be achieved?
Monthly data: I believe in general you may be able to email yourself a monthly PDF report based on a slice that selects all the monthly records. Also it may be better to run the report in the next month ( say in first week of next month or after the monthly window you have is complete) with a slice based on previous month’s records so that you are sure that no record is omitted just in case.
For entire spreadsheet as well as monthly records, yes , in general, I believe you may email yourself a PDF report for all records. However I believe there is obviously a limit on PDF size ( I believe it is 20 MB, which ahould be substantial) However, this limit implies that you may not have extremely large number of records in any single report.
Yes. I want just what you have described. Also the report would be sent next month (specifically after 15th of next month as users are often late in entering data), and there’s no way the number of reports will be large enough so as the file size exceeds 20 MB.
Please help me on how I may achieve that.
You may wish to take a look at the following article that describes the reports concepts as well reports creation process in detail
As a starter I have made a column that outputs the information specific to that record’s date, in terms of its month and year. month([Date]&year([Date]). The output for today would be 32020.
Also I think that the report / e-mail would be triggered based on the condition that the month:year column for that record matches (month(today())-1)&year(today()).
Am I walking the right path?
Can you please give me a little more than that? Just tell me whether I’m right or wrong in terms of the starter I have described above.
Also for the trigger, it is required to change a value in a specific column. Since the report I am trying to generate is not row level, how do I use that specific value change?
I believe, generally it looks OK. However you will need to create a slice expression in such a way that the slice selects 32020 ( I mean March 2020) records in April because you are firing report in next month. If you wish that to be a seamless automatic process , the slice expression may need to be thought of based on fact that you will also have year change and need to run 122020 reports in Jan 2021 , that is 12021
if you wish to run reports by periodic frequency automatically (monthly on a specific day) , then you may use the option under Behavior ->Report
If you wish to manually trigger reports through a click by the user, you may wish to use Behavior --> Workflow option.
For sending report through a click ( meaning reports on demand) , please take a look at the following sample app
For reports on demand, is it possible to send the report for a whole spreadsheet rather than row-specific data? The app you told me to have a look at does that.
Yes, sure you can do that. I presume you mean all records in a table by the whole spreadsheet.
The table contains all data. I want to send just the previous month’s data using the conditional filter I described.
Yes, you can do that.
Well I have to get more specific. The spreadsheet which I want to send the data from contains columns that have " Date, supplier name, supplier ID, bill amount ". I added an extra column to do that 32020 thing.
Now as far as I have understood, triggering a report would require updates / add to a spreadsheet. I am adding the ‘target data’ as the spreadsheet which contains the information I mentioned above (" Date, supplier name, supplier ID, bill amount "). That’s a sheet that works on importrange() in gsheets, and is updated automatically as users enter data. So if a report was to be triggered everytime an add or an update took place, the reports would get sent multiple times everyday.
What is done in the app that you told me to get a look at is it has an e-mail form which when has an addition , triggers the report generation. But how can I achieve this in my case? I have no way to incorporate an “ADD / UPDATE” in the spreadsheet which I want to send other than updates which happen when users enter data.
I hope you get me.
Request you to note the difference between scheduled reports and on demand reports as described above ( highlighted below) .
Triggering a scheduled report need not have updates / add to a spreadsheet just to trigger the report. It will trigger on the scduled frequency (daily, monthly etc.) and include the records in the table or slice that is included in the report, template expression. (One example is you can have previous month reports through a slice with an appropriate expression or you may have report template expression to select previous month’s records)
As per my understanding, you may not really need a separate spreadsheet just for selecting the target data for the report. As described above, in general, you may use template expressions or filtering based on slices to selecte the desired records for the report
The sample app I had shared was an alternative , in case you wish to fire on demand/ ad hoc reports. On demand reports are fired by manual intervention by the user through actions. Even there you can select the desired records by using template or slice filtering expressions.
The sample app uses only one column as a trigger column ([ Email Sent]column in Orders table) to fire the report on demand by the user. It will not trigger when other data in the row is changed or added. Please look at the Orders Table and “Orders On change” workflow for the same. Even though sample report template consists of only single record, you can include a SELECT() with filter template expression or slice to select multiple reocrds in the report.
You may also find the article below useful for on demand reports
I got most of it now. Thanks a lot.
Just one more thing (as of now at least :-D), how do I format the template as per my need?
I want the data to be in table form as in the spreadsheet itself and maybe some a couple of columns to get a total.
Could you please further elaborate. You could construct the template to have the table and respective fields in the table cells . A good way in general to start is as per my understanding , use the create option in the workflow or reports pane and then use the created template to further edit it.