[Feature Request] Add functionality to update Sheet metadata, add additional tabs to Sheet and copy a Sheet to a new tab (vs a whole new Sheet)

[queks]

The example given in https://github.com/scrthq/PSGSuite/wiki/Copy-GSSheet shows how to copy a sheet to a new sheet by providing the SourceSheetID. The parameter in the function indicates the SourceSheetID is a string, but i get the following error when copying from a sheet i titled 'Staging':

Copy-GSSheet : Cannot convert argument "sheetId", with value: "Staging", for "CopyTo" to type "System.Int32": "Cannot convert value "Staging" to type "System.Int32". Error: "Input string was not in a correct format.""

[queks]

Oh, so i figured out that the sheetID refers to the GID. but when i insert the right value and try to copy to a new sheet now i get an error:

Select-Object : The property cannot be processed because the property "Title" already exists.

[scrthq]

@queks - got it! checking it out!

[scrthq]

@queks - if you include the -Raw switch when running, i.e. Copy-GSSheet -Raw ...., does it still throw?

[queks]

Yes, that works to create a new google spreadsheet document.

So is it possible to create another sheet (tab) within the same spreadsheet? Say if i wanted to run a script regularly and upload the data to a different sheet tab each time for comparison, could i create a sheet dynamically when uploading the data?

I was looking at Export-GSsheet, but it doesn't seem to allow that, so was trying to use Copy-GsSheet to do that instead, but what it does is create a whole new document instead, with a new URL.

[scrthq]

@queks - 2 things:

1. Can you give me an example command where you're able to replicate it? Feel free to replace the parameter values with dummy variables, I just want to see the parameters you're using to replicate so I can fix that error since that is a bug.
2. I don't believe there is a PSGSuite function yet to add a new Sheet ("tab") to a SpreadSheet, so I'm taking that as a feature request.

[queks]

Here's what I'm trying to do. I run a script to go through 900 student blogs and grab highlighted items for their portfolios. I capture the embed codes for these items and store them in a table together with their student ID, grade, etc.

But these blogs update over time, so I run the script regularly, especially closer to the end of the semester. Each time i run the script, i upload it to a google spreadsheet as a different tab with the date/time of the execution as the tab name. This is also for teachers to cross-reference to make sure the right items are being picked up.

My initial try was to create a new tab with the date/time as the sheet name, but there isn't a feature to do that. So I went ahead with a tab called 'Staging' which I will then use to copy to a new tab. I managed to do that by specifying the same ID for both source and destination spreadsheets, but i still can't pre-define the name of the tab.

This is the command that I'm running:
Copy-GSSheet -SourceSpreadsheetId '1oSkEceeKaWhaSvLt2SuvS4f9g747gCzeIIj284FRNlr' -SourceSheetId 1694250000 -DestinationSpreadsheetId '1oSkEceeKaWhaSvLt2SuvS4f9g747gCzeIIj284FRNlr' -Raw

but i can't use -NewSheetTitle to define the new tab's name or it will throw an error.

[scrthq]

Thanks, @queks!

The error you're getting when including -NewSheetTitle is the following, right?

Copy-GSSheet : Cannot convert argument "sheetId", with value: "Staging", for "CopyTo" to type "System.Int32": "Cannot convert value "Staging" to type "System.Int32". Error: "Input string was not in a correct format.""

[queks]

No, its:

Copy-GSSheet : Parameter set cannot be resolved using the specified named parameters.

[scrthq]

@queks - Got it. It's working as intended then, given the functionality of either parameter set on the function. The reason you're getting that error is you can't specify -NewSheetTitle and -DestinationSpreadsheetId parameters at the same time since they belong to different parameter sets. Here are both parameter sets visible from the functions help contents, you can see those 2 parameters each in their own parameter set:

The current functionality of Copy-GSSheet is to do one of 2 things:

1. Copies the data from the specified SourceSpreadsheetId and SourceSheetId to a brand new Google Sheet titled with the value passed for NewSheetTitle.
2. Copies the data from the specified SourceSpreadsheetId and SourceSheetId to an existing Google Sheet whose ID is the value passed for DestinationSpreadsheetId.

What you're looking to do is manage the tabs/sheets within a single Google Sheet, which PSGSuite is not capable of doing yet (but will be soon).

I'll keep you updated!

[queks]

Got it, thanks!

btw, while you're working on that, is there a way to also allow the renaming of sheets and/or tabs?

thank you!

[scrthq]

@queks - can do! updating your original issue description to clarify what's being asked

[scrthq]

Hey @queks - Just want to follow up and say I haven't forgotten about this request, just haven't had time to implement! I'll keep you updated as soon as I have an update to provide! Thanks for your patience around this!

[scrthq]

Hey @queks - Just a heads up, should have this finished up within the next week! I'll keep you updated!

[queks]

Thanks! I've been busy too, so no worries!

[FISHMANPET]

Hey @queks It's been over 3 years, but I think there are finally some additions that will make this a lot easier.
The next release will include a bunch of helper functions that make it much easier to interact with all aspects of the Spreadsheets API, by creating functions (automatically generated) for everything for the BatchUpdate endpoint. This is for formatting and other changes to the structure of the Spreadsheet, not the data itself.

I've also added some helper functions that use those generated functions to simply some common operations around sheets - Add-GSSheetSheet, Remove-GSSheetSheet, Move-GSSheetSheet, and Rename-GSSheetSheet. These allow you to easily add or remove sheets (or "tabs"), rename sheets, or move a tab within the tab list. In your case it sounds like you would need to simply add a new tab, or rename an existing one, to put new data in your sheet. And the coming release should make this much easier for you.

If you have more advanced needs, it should be possible to meet them using the auto-generated Add-GSSheet* functions along with Submit-GSSheetBatchUpdate.

The initial function you were trying, Copy-GSSheet, uses the spreadsheets.sheets.CopyTo API, but it looks like DuplicateSheetRequest used with batchUpdate is more powerful. In the future that function might be extended or rewritten, but for now it's untouched.