[gamadv-xtd3] todrive, tdfileid, tdupdatesheet true

[Dominik Kugelmann]

Hi folks,

I am trying to build an automated report based on gam report meet

I am wondering if tdfileid and tdupdatesheet true will keep data older than 90 days due to the sheet being updated rather than overwritten.  I assume that is the behaviour but I don't have enough confidence to deploy it this way.  I'd like to keep a spreadsheet of all data, even data older than 90 days. 

Is that something that is possible with this command combination?

My example command:

gam report meet todrive tdfileid "<id>" tdsheet "id:0" tdupdatesheet true 

Thanks and stay safe! 

Servus and Goodbye,

Dominik

[Ross Scroggs]

Dominik,

No, this will not do what you want. tdupdatesheet means update the sheet in place with the new data, no previous data is retained.

Ross

--

ross.s...@gmail.com

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAPyiHiNfBjMWreFOtF7cCr1tRHTO3yJTVDFr7ymw7H2mTZsgJg%40mail.gmail.com.

[Dominik Kugelmann]

so, if I tdupdatesheet false I get the desired result? 

Or is that just not possible? 

[Ross Scroggs]

Dominik,

See: https://github.com/taers232c/GAMADV-XTD3/wiki/Todrive

Currently, there is no way to append data to an existing sheet.

Ross

--

ross.s...@gmail.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/b05c24eb-df11-418b-a51a-f403a5e6a06e%40googlegroups.com.

[+KimNilsson]

But, since Ross is awesome... there may soon be! :-)

[John Krug]

Came here for the same info. Add to the list of folks who would love to see the ability to append rows to an existing sheet. I get that the API is the limit.

[John Krug]

I dislike that each tab in a sheet is called a sheet. The lack of distinction between them makes some of this discussion more difficult. To clarify, one can not add a sheet (tab) to an existing sheet using todrive?

[Ross Scroggs]

John,

See: https://github.com/taers232c/GAMADV-XTD3/wiki/Todrive#update-existing-file-with-a-new-sheet

See: https://github.com/taers232c/GAMADV-XTD3/wiki/Todrive#update-an-existing-sheet-within-an-existing-file

Read carefully, test.

Do these get you what you want?

Ross

--

You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/c9053051-654f-40dd-bca4-4be1e0077f83n%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Kim Nilsson]

Only if this is possible, Ross.

Create a new sheet within a Spreadsheet without touching other sheets.

Update that new sheet with content, without touching other sheets.

OR

Create a new sheet within a Spreadsheet and fill it with content without touching other sheets.

I read your documentation earlier, and to me it looked like, if you create a new sheet it deletes everything else in the spreadsheet.

And the goal is to upload fresh data in a new sheet, but not destroy data in the other sheets of the same spreadsheet.

/Kim

[Ross Scroggs]

KIm,

See: https://github.com/taers232c/GAMADV-XTD3/wiki/Todrive#update-an-existing-sheet-within-an-existing-file

Ross

--

You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAGPzzFufd-JBsSzXs0_WCvp7B_MryBORqGWdQ52nHZ4Vqh18Mg%40mail.gmail.com.

--

Ross Scroggs

ross.s...@gmail.com

[Kim Nilsson]

Yes, but there is it scaringly written...

tdsheet <String> - A new sheet with name <String> will be created and assigned a new sheet ID. All other sheets will be deleted.

We don't want that.

/Kim

[Ross Scroggs]

Kim,

There are two sections:

https://github.com/taers232c/GAMADV-XTD3/wiki/Todrive#update-existing-file-with-a-new-sheet

• tdsheet <String> - A new sheet with name <String> will be created and assigned a new sheet ID. All other sheets will be deleted.

https://github.com/taers232c/GAMADV-XTD3/wiki/Todrive#update-an-existing-sheet-within-an-existing-file

• tdsheet <String> - An existing sheet of the same name will be updated and retain its sheet ID. All other sheets are unaffected.

Ross

--

You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAGPzzFtuJS6p%3Dk-ft2NRpL-a%3D8WGBugNfZ%3Dcd5o5FqEFhx1k2w%40mail.gmail.com.

--

Ross Scroggs

ross.s...@gmail.com

[Kim Nilsson]

Yes, but...

Maybe I'm not understanding.

Step 1, we have a spreadsheet with lots of sheets.

Step 2, we want to add new data to it.

Step 3, we need to add the new data in a new sheet. Not to any of the existing sheets.

Step 4, how do we add/create a new sheet in the existing spreadsheet without touching any existing data?

[Ross Scroggs]

Kim,

I get it now, you can't currently do this, I'll have to speak to the programmer.

Ross

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAGPzzFuX2f569gF0p53Q6mUz7V4Zg09jCzNvuUzr9MuT-fjqXQ%40mail.gmail.com.

--

Ross Scroggs

ross.s...@gmail.com

[Ross Scroggs]

Kim/John,

With Advanced GAM 5.23.04 you can now add a sheet (tab) to an existing spreadsheet file.

See: https://github.com/taers232c/GAMADV-XTD3/wiki/Todrive#add-a-new-sheet-to-an-existing-file

Ross

--

ross.s...@gmail.com

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAGPzzFuX2f569gF0p53Q6mUz7V4Zg09jCzNvuUzr9MuT-fjqXQ%40mail.gmail.com.

[Kim Nilsson]

Remember what I said, guys!

On Saturday, 11 April 2020 at 18:30:45 UTC+2 Kim Nilsson wrote:

But, since Ross is awesome... there may soon be! :-)

[John Krug]

Well, well, well. This is a lovely development!

[John Krug]

Is there a way to name the new sheet?  I can use tdtitle, but that renames the enclosing/master sheet.

Not a big deal, I'm already happy. Just a "nice-to-have" if possible.

Thanks.

[Kim Nilsson]

John, try also using tdsheet <String>.

[Ross Scroggs]

tdsheet <String> applies to tdupdatesheet. I'm adding tdaddsheettitle <String> that will apply to tdaddsheet,

Ross

On Thu, Oct 29, 2020 at 2:07 PM Kim Nilsson <there.is.no...@gmail.com> wrote:

John, try also using tdsheet <String>.

--

You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/39417612-7d0a-4b7a-b44a-c27a6785df10n%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Ross Scroggs]

John/Kim,

5.23.05

Added option `tdaddsheettitle <String>` to `<ToDriveAttribute>`.  When used with `tdaddsheet`,

the new sheet (tab) can be named independently of the existing Google spreadsheet file.

Ross

--

Ross Scroggs

ross.s...@gmail.com

[Kim Nilsson]

Ross, as Father Christmas. 

[James SEYMOUR]

Hi Ross,

Thanks for this, I can see lots of uses coming up. As ususal I have been having a 'play' to see how I might put this to work. Is there a way to have a different time stamp for the Google Sheet compared to the added tab/sheet? At the moment they follow the same and controlled by tdtimeformat.

My use-case example (potentially, although I know this will not be very accourate reporting) would something like this command;-

gam report customer parameters gmail:num_emails_sent,gmail:num_emails_received range -32d -2d todrive tdfileid 1MFsxxxxxxxxxxxxxxxxxxgLitoB4 tdtitle "Monthly Email Report" tdtimeformat '%Y-%m-%d' tdaddsheet tdaddsheettitle "Usage". This could be run every 30 days to get a 'monthly report' or some combination similar.

However, the current commands will update a Google Sheet name such as "Monthly Email Report - 2020-10-30" (which is OK as it shows the last time the Google Sheet was changed) and the new tab/sheet name is "Usage - 2020-10-30". But if the timestamp for the new tab/sheet could be supressed and/or changed so that you could use, for exmple, %B %Y, then the name of the newly added tab/sheet would be "Usage - October 2020" etc etc.

Just asking as I know you like a challange:)

James

On Friday, 30 October 2020 at 08:34:43 UTC+8 there.is.no...@gmail.com wrote:

Ross, as Father Christmas. 

[Kim Nilsson]

Ohhhh, that's a great idea.

Having a choice to put a timestamp in the sheet title and not only in the file title.

/Kim

--

There is No Substitute!

[James SEYMOUR]

Just off a call from Ross and he has confirmed this is possible using the option (tdsheettimestamp [<Boolean>] [tdsheettimeformat <String>]) (https://github.com/taers232c/GAMADV-XTD3/wiki/Todrive#command-line-options)

So for example, this command, each time is is run will add a new sheet, which if run this month the tab/sheet name will be "Month Report - November 2020", which the File name will be something like "Cros DeviceID - 2020-11-18".

gam crosqueries "status:active" print cros todrive tdfileid <Existing File ID> tdtitle "Cros DeviceID" tdtimeformat %Y-%m-%d tdaddsheet tdsheet "Month Report" tdsheettimeformat "%B %Y"

[Kim Nilsson]

Thanks, James.

That means you can schedule such commands (cronjob or other scheduler depending on the OS) and always see on the title when it was last run, and the separate sheets will have unique data and names. 

[Kim Nilsson]

@Ross,

A bit greedy perhaps, but I would like to only name the new added sheet according to the timestamp. No sheetname except for the timestamp.

So I can upload a new dated sheet, named only from the tdsheettimeformat, so I can schedule a new daily, weekly, monthly sheet.

Currently, you get some default sheetname (derived from what type of gam command you run) or the same name as the title of the file, and then your timestamp.

I want to be able to choose to only get the timestamp as the name of the new sheet.

[Kim Nilsson]

One other reason is that if you have a very long tdtitle, you can't create a new sheet based off that name, as individual sheets can't have filenames longer than 100 chars. (I just learned, as it crashed for me :-) )

[Kim Nilsson]

I can fake (close, but no cigar) my request by adding tdsheet "."

Then I will get a sheet called ". - MyTimestamp", and then I have to delete the ". - " bit manually.

Not as cool as only getting a timestamp-title. :-)

[James SEYMOUR]

@Kim, Ross has fixed the tab/sheet name issue so, for example, if you specify tdaddsheet tdsheet "" tdsheettimeformat "%B %Y" the tab/sheet name will be, for example, December 2020.

On top of that, there are some new (I think) report options (which Ross contacted me about) yesterday|thismonth|(previousmonths <Integer>)] where the Integer is between 1 - 6 (months back). So if you combine this with the todrive option tddaysoffset, it is possible to automatically run (cron/sheduled task) a command at the start of the month to collect data, add a new tab/sheet to an existing a Google Sheet of the previous months data.

For those who would like an example, running this today, 3rd December 2020;-

gam report usage customer parameters meet:total_call_minutes,meet:total_meeting_minutes skipdaysofweek sat,sun previousmonths 1 todrive tdfileid <File ID> tdtitle "Meet Usage" tdaddsheet tdsheet "" tdsheettimeformat "%B %Y" tddaysoffset 6

Breakdown

gam report usage customer parameters meet:total_call_minutes,meet:total_meeting_minutes - The GAM command

skipdaysofweek sat,sun - exclude Sat & Sun, so only working days

previousmonths 1 - run against the previous months date range (regardless of how many days in the month, leap year etc)

todrive tdfileid <File ID> tdtitle "Meet Usage"- write the data to an existing Google Sheet called "Meet Usage"

tdaddsheet tdsheet "" - Add a new tab/sheet with no name

tdsheettimeformat "%B %Y" tddaysoffset 6 - give the new tab/sheet a time stamp backdated by 6 days of 'Month Year', so for this example "November 2020", which will become the name of the new tab/sheet. The offset number must take you back in time into the previous month.

@Ross, fantastic additions to the GAM options. Thanks.

[Kim Nilsson]

Great writeup of an example, James.

One thing to note, though. 

tdtitle is an active set of the name, it doesn't look for the file name. It also needs to be used, else it will be set to a default name on each run. 

[James SEYMOUR]

Hi Kim,

Thanks for the clarification on tdtitle.

One thing I forgot to mention is that the drawback of the tddaysoffset option is that it affects all the timestamps. I was hoping to have the Google Sheet Name to be, for example, "Meet Usage 2020-12-03" (the date the file was updated), while the new tab/sheet name would be "November 2020" (the date of the data). However, the tddaysoffset means that the Google Sheet name becomes "Meet Usage - 2020-11-27", 6 days ago, if you add a timestamp to it.

Ross, not sure if there is a way to have a different tddaysoffset for the file name against the tab/sheet name?

Cheers

James

[Ross Scroggs]

James,

See: https://github.com/taers232c/GAMADV-XTD3/wiki/GamUpdates

Ross

--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/4506c5ec-91e2-4252-b69c-eed87c9e29aen%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[James SEYMOUR]

@Ross, WOW, Fantastic. Many thanks for this.

So an update (highlighted in bold) to my example, running this today, 3rd December 2020;-

gam report usage customer parameters meet:total_call_minutes,meet:total_meeting_minutes skipdaysofweek sat,sun previousmonths 1 todrive tdfileid <File ID> tdtitle "Meet Usage" tdtimeformat %Y-%m-%d tdaddsheet tdsheet "" tdsheettimeformat "%B %Y" tdsheetdaysoffset 6

Breakdown

gam report usage customer parameters meet:total_call_minutes,meet:total_meeting_minutes - The GAM command

skipdaysofweek sat,sun - exclude Sat & Sun, so only working days

previousmonths 1 - run against the previous months date range (regardless of how many days in the month, leap year etc)

todrive tdfileid <File ID> tdtitle "Meet Usage"  tdtimeformat %Y-%m-%d - write the data to an existing Google Sheet and append with current date, so it will be called "Meet Usage - 2020-12-03"

tdaddsheet tdsheet "" - Add a new tab/sheet with no name

tdsheettimeformat "%B %Y" tdsheetdaysoffset 6 - give the new tab/sheet a time stamp backdated by 6 days of 'Month Year', so for this example "November 2020", which will become the name of the new tab/sheet. The offset number must take you back in time into the previous month.

Notes;-

You need to have already created the Google Sheet, to get the <File ID>. And tdtitle is not optional, although you should be able to specify "" (a blank name) if you just want the Google Sheet to show the updated date.

Off to set up some cron jobs now :)

[James SEYMOUR]

I have also added this to the Reports page of the Wiki https://github.com/taers232c/GAMADV-XTD3/wiki/Reports#monthly-report

[Kim Nilsson]

Thanks, James!

I always recommend your site to new GAM users as a source for great examples of code, explained. 

[James SEYMOUR]

Thanks Kim, much appreciated. It is my way of trying to give something back to the community. I might not be the best 'coder' but once explained I do understand the commands and like to keep a record of them, for me and others to use.