Copy-GSDriveFile Google Sites application/vnd.google-apps.site

[Foggy2]

Describe the bug I am not 100% sure if this is a bug or just correct Drive API behavior. However, I have been attempting to use the Copy-GSDriveFile function to create a copy of a Google Sites file.

This is the result when I try to copy.

To Reproduce Steps to reproduce the behavior:

1. Create a Google Sites file and attempt to copy it.

Expected behavior A seperate copy is made in the nominated location.

Additional context I also received the same error when attempting a copy using the Drive API explorer found here: https://developers.google.com/drive/api/v3/reference/files/copy

The API reference seems to indicate that the mimetype application/vnd.google-apps.site for Google Sites is supported however it is quite vague. https://developers.google.com/drive/api/v3/mime-types

I also tried some quick Googling but didn't uncover many relevant results. Figured that someone here might have a better understanding of the API and whether or not this is correct behavior.

[scrthq]

Hey @Foggy2,

• Is this on the old Google Sites or the new one?
• Are you able to perform a Get-GSDriveFileInfo against the same FileId?
• Are you the owner of the Site?

I'm trying to replicate on my end, but I'm able to copy and get info of a Site file without issue as an owner of the Site. The site is built on the new Google Sites, which should be a requirement for this usage since the old Google Sites do not store their files in Google Drive the same as the new Sites does.

[Foggy2]

Hi @scrthq,

• This is using the new Google sites.
• Output of the Get-GSDriveFileInfo is below:
• The site is located in a team drive of which I have been added to as a 'manager'.

To rule out the Team Drive being the issue I have just copied the sites file using the web interface and moved the copy into the root of my personal drive. The output of the Get-GSDriveFileInfo command for the copied file is below:

I have also tried to create a copy of the file in my personal drive and received the same error.

As I did not create the sites file. I have also just attempted creating my own sites file directly and saving that in my drive. Attempting to copy it resulted in the same 403 error.

Do you think it could be a scoping issue again?

[scrthq]

Sorry for the late reply, @Foggy2!!! Been a bit slammed. It very well could be a scope issue and I haven't had the chance to test the Team Drives aspect yet myself. Based on the error though, I don't think it's a scope issue tbh. You'd get a client related error in that case, I believe.

Can you try re-running that Copy-GSDriveFile command in your last screenshot, but leave off anything outside of the FileId and User parameters (i.e. just run it with the bare minimum)?

Also, is your User you're passing the same as your AdminEmail or a different user?

[scrthq]

And we have replication! This is when trying to copy a Site within a Team Drive. Doing some digging to see if it's Team Drive related, I know that copying things within Team Drives itself has it's limitations even in the UI

[Foggy2]

Hi @scrthq,

Thanks for looking into this. I have also been quite busy the last 1-2 weeks with the start of the Australian school year this week.

Glad you could replicate it as it confirms that it isn't just me.

When running the command, I was using my personal account in the user field, not the admin account that the API access is delegated to.

As requested I have re-run the Copy-GSDriveFile command omitting the other parameters. Unfortunately it still failed.

I have also noticed that the capabilities reported by the Get-GSDriveFileInfo command indicates that copying is not allowed.

The interesting thing about the tests from this post is that the file I am testing with, 1ceDjb4SCTaqjGXSGI8MeoG9E8UiOHm4B is a site that I manually created in my drive. From your post above you indicated that you were able to copy a site in your drive but I do not seem to be able to do the same thing in mine.

[scrthq]

Hey @Foggy2 - Nice catch on the CanCopy flag! These last few weeks have been dragging but I'm hoping to hop back on this by the weekend. I'll let you know! Thanks for the updates as well, super helpful!

[scrthq]

Hey @Foggy2 - No updates here yet, still working on this.

[Foggy2]

No worries @scrthq. This isn't an important issue on my end so please don't feel as though you need to prioritise it.

From what I could find out about the issue I have all but accepted that it can't be done with the current APIs that are provided. So if you can get it working great, but I am happy if you don't think it can be done and you want to close the issue to focus on other things.

[jeffreymcclain]

@Foggy2 @scrthq I know it's been a year, but any update on this?

I'm encountering a similar issue with the "Import-GSSheet" cmdlet, after passing it a spreadsheet ID I get the following error:

Import-GSSheet -SpreadsheetId [ID]

Import-GSSheet: Exception calling "Execute" with "0" argument(s): "Google.Apis.Requests.RequestError Unable to parse range: [400] Errors [ Message[Unable to parse range: ] Location[ - ] Reason[badRequest] Domain[global] ]

I entered the same spreadsheet id for the "Get-GSSheetInfo" cmdlet and it worked fine, so I'm assuming it is related to the way the cmdlet formats the request.

[FISHMANPET]

Does the spreadsheet you're importing have data that extends beyond the column headers in the first row? There's a bug with that, I've got a fix but I haven't merged it in yet.

[jeffreymcclain]

@FISHMANPET I created a new spreadsheet and entered some text in a couple of the boxes (A1, A2, B1, and B2). Is there a certain format I should follow for the cmdlet to work?

[FISHMANPET]

Here's a spreadsheet that doesn't work, as an example: https://docs.google.com/spreadsheets/d/1hMt5lp3gN6XK7yA6TPJU8BdYJ1NiMyNqqxOq0NVkQUw/edit#gid=0 It fails because of cell D3, because there's no header it doesn't handle that case well. If you've got just A1, B1, A2, B2, that should be fine, but can't be sure without seeing the data.

[jeffreymcclain]

This is the test spreadsheet I just created: https://docs.google.com/spreadsheets/d/1WEQAxLaIDBV7nQvX9ixg1ONSKx9BoeK5PwugfO29wZE/edit?usp=sharing

Can you copy to your own Drive and verify if this command works:

Import-GSSheet -SpreadsheetId '1WEQAxLaIDBV7nQvX9ixg1ONSKx9BoeK5PwugfO29wZE'

It could be maybe I didn't setup the project correctly in the first place, or maybe something with my permissions.

• Jeffrey McClain

On Wed, Apr 15, 2020 at 1:16 PM Peter Bajurny [email protected] wrote:

Here's a spreadsheet that doesn't work, as an example: https://docs.google.com/spreadsheets/d/1hMt5lp3gN6XK7yA6TPJU8BdYJ1NiMyNqqxOq0NVkQUw/edit#gid=0 It fails because of cell D3, because there's no header it doesn't handle that case well. If you've got just A1, B1, A2, B2, that should be fine, but can't be sure without seeing the data.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/SCRT-HQ/PSGSuite/issues/148#issuecomment-614258079, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACPBSIC3VJ5ECRUJ7UMGHF3RMYI2LANCNFSM4GQ3QVAA .

[FISHMANPET]

I got this error on that spreadsheet:

Import-GSSheet : Exception calling "Add" with "1" argument(s): "Input array is longer than the number of columns in this table."
At line:1 char:9
+ $test = Import-GSSheet -SpreadsheetId '1hMt5lp3gN6XK7yA6TPJU8BdYJ1NiM ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Import-GSSheet

Which is different from the error you first described, but there shouldn't be a problem with that test spreadsheet as is, so I'll look into that.

[jeffreymcclain]

@FISHMANPET Thanks for the help troubleshooting, I really appreciate it!

As a follow-up, I ran the same command with the "verbose" parameter. I notice the "range" value is null / empty by default, I'm not sure if that might be related to why the command fails or if it's just a coincidence.

As a workaround, feel free to share an example template in the correct format. For testing I literally have just been going to Google Drive, clicking "New > Google Sheets > Blank Spreadsheet", and then entering test values in A1, A2, B1, and B2.

[FISHMANPET]

oh, I should have seen that in your first command, you need to specify which sheet you're reading from (even if the spreadsheet only has 1 sheet. So you would want something like this: Import-GSSheet -SpreadsheetId [ID] -SheetName 'Sheet1' Even then I'm getting my different error on your test spreadsheet that I don't get when I copy the same data into another sheet, so something is weird there. Also I'll checkout the parameter sets to ensure that the sheetname is mandatory when needed.

[jeffreymcclain]

Ah ok, I didn't realize that "SheetName" was a required parameter.

For reference, I re-ran the same command with the sheet name. I'm going to create a new Google account specifically for testing, then reinstall the PowerShell module and follow the "initial setup" instructions precisely (granting all requested permissions) to make sure that isn't the issue.

My only other thought is could it be an issue with PowerShell 7? Typically I use the built-in PowerShell 5.1, but I saw in the instructions that PowerShell core was supported and recommended for generating the permissions request URL to copy / paste into Chrome.

To verify, you are saying the command works fine for you by simply creating a blank Sheet and then entering / copy-pasting the same data in? I just want to make sure I'm not missing anything obvious like formatting, I tried to keep the test as simple as possible by just entering values directly into a few cells.

[FISHMANPET]

SheetName is the name of the Sheet in the spreadsheet, not the name of the Spreadsheet. In your test sheet it's Sheet1 so this command should work: Import-GSSheet -SpreadsheetId '1WEQAxLaIDBV7nQvX9ixg1ONSKx9BoeK5PwugfO29wZE' -SheetName 'Sheet1' And now that... works for me. I think I was accidentally grabbing my broken sheet instead of your sheet to generate my error message above.

[FISHMANPET]

It's a little confusing but the Spreadsheet is the entire Google document, the Sheet is the specific tab in the Spreadsheet, because a spreadsheet can have multiple sheets.

[jeffreymcclain]

Awesome, I can confirm

Import-GSSheet -SpreadsheetId '1WEQAxLaIDBV7nQvX9ixg1ONSKx9BoeK5PwugfO29wZE' -SheetName 'Sheet1'

worked successfully for me!

Thanks so much for the help! I suspected the issue might have just been me being an idiot and not using the right values / syntax, lol.

Since it sounds like you help maintain the module, something I think could be useful would be more descriptive error messages / error handling, e.g. "Error: Verify you entered the sheet name, NOT spreadsheet name!". I also appreciate that you are planning to mark "SheetName" as a required parameter as well.

[scrthq]

@FISHMANPET Thank you for the assist here!! @jeffreymcclain - Valid feedback, could you please open that up on another issue for better tracking? What you're looking to do would require an additional API call to pull down the sheet info and compare, and there's technically no restriction on naming the Sheet the same as the overall Spreadsheet/Workbook, so reaching that conclusion reliably may be difficult. What I'm imagining as a workaround would be adding some parameter aliases that more closely align with what you expect, e.g. WorkbookName and TabName.

[jeffreymcclain]

Done, opened issue https://github.com/SCRT-HQ/PSGSuite/issues/279

Thanks again both of you for all your help!