Exporting User´s Google Docs

[chrispaivacwb]

Is your feature request related to a problem? Please describe.

I would like to have the ability of exporting google docs users to files as described:

• Google Sheets -> .xlsx
• Google Docs -> .docx
• Google Presentations -> .pptx
• and so on...

Describe the solution you'd like

Do a recursive search in a specific drive location (personal, shared drives or both) for all user or specific one and then export all the files found on its source location.

Describe alternatives you've considered

Additional context

[scrthq]

Hey @chrispaivacwb - Looking into this now!

[scrthq]

Hey @chrispaivacwb - I've looked into this and the only way to do it (that I can see) is by downloading the file locally first using Export-GSDriveFile as your desired type, then uploading as a new Drive file. Not the most ideal scenario, unfortunately.

I'll get you some sample code once I have a moment if interested!

[chrispaivacwb]

@scrthq I really need this feature and don´t have the skills to have a fully functional script running recursively on all my files for all my users. As I had mentioned I am doing a full site migration to O365 and this is the most usefull feature I could have instead of converting all Google docs files manually.

[scrthq]

hey @chrispaivacwb - Here's some code to pull down your users exportable Google Drive file types in user specific folders. This will first list all of the non-suspended users in your account, then search each users' Drive for files they own that are within the mimeTypes in scope (Sheets > xlsx, Docs > docx, Slides > pptx), then download them to a folder with their primary email address as the folder name. Let me know if you have any questions regarding this!

Import-Module PSGSuite

# This is the folder that you will be downloading the Drive files to
# Adjust as needed
$stagingFolder = "C:\DriveFileStaging"

# Set this to $true if you want to append the ID of the file to the resulting file name
# Set this to $false if you want to leave as-is (could result in collisions if there are multiple files owned by the user with the same name and mimeType)
$includeIdInFileName = $true

# Get list of enabled users
$users = Get-GSUser -Filter "IsSuspended -eq '$false'" -Verbose

$formats = @(
    @{
        MimeType = 'application/vnd.google-apps.spreadsheet'
        Extension = 'xlsx'
        ExportType = 'MSExcel'
    }
    @{
        MimeType = 'application/vnd.google-apps.document'
        Extension = 'docx'
        ExportType = 'MSWordDoc'
    }
    @{
        MimeType = 'application/vnd.google-apps.presentation'
        Extension = 'pptx'
        ExportType = 'MSPowerPoint'
    }
)

# Loop through each user > 
$u = 0
foreach ($user in $users) {
    $u++
    $userFolder = [System.IO.Path]::Combine($stagingFolder,$user.PrimaryEmail)
    if (-not (Test-Path $userFolder)) {
        New-Item $userFolder -ItemType Directory -Force
    }
    foreach ($format in $formats) {
        try {
            # pull down Drive files they own with a Google Apps MimeType
            $files = Get-GSDriveFileList -User $user.PrimaryEmail -Filter "'$($user.PrimaryEmail)' in owners and mimeType='$($format.MimeType)'" -Fields 'files(mimeType,id,name,owners,ownedByMe)' -Verbose -ErrorAction Stop -IncludeTeamDriveItems
            $f = 0
            foreach ($file in $files) {
                $f++
                try {
                    # Get safe file name based on mimeType extension and whether or not to include the ID in the FileName
                    $uniqueName = if ($includeIdInFileName) {
                        @($file.Name,$file.Id) -join "_"
                    }
                    else {
                        $file.Name
                    }
                    $fileName = ($uniqueName -replace "[$([RegEx]::Escape("$(([System.IO.Path]::GetInvalidFileNameChars() + [System.IO.Path]::GetInvalidPathChars()) -join '')"))]","_") + "." + $format.Extension
                    Write-Host -ForegroundColor Green "[$($user.PrimaryEmail)] [User $u\$($users.Count)] [File $f\$($files.Count)] Exporting file: $fileName"

                    # Get final file path
                    $outPath = Join-Path $userFolder $fileName

                    # use Export-GSDrive to export them as the non-Google type
                    Export-GSDriveFile -FileID $file.Id -User $user.PrimaryEmail -Type $format.ExportType -OutFilePath $outPath -Verbose -ErrorAction Stop
                }
                catch {
                    Write-Warning "[$($user.PrimaryEmail)] [$($fileName) :: $($file.Id)] Failed to export Drive file! Error: $_"
                }
            }
        }
        catch {
            Write-Warning "[$($user.PrimaryEmail)] Failed to list Drive files for this user! Error: $_"
        }
    }
}

[scrthq]

@chrispaivacwb - The only bit that the above does not do is handle reuploading as the new file type. Let me know if you'd like that as well or if the provided code is sufficient.

[chrispaivacwb]

@scrthq It works perfect well.... I don´t think it is necessary to reupload them. It would be nice to have them in the same directory tree structure, but I am really satisfyed having them converted locally in one folder by now.. I am really thankful for all your support.

[scrthq]

I'm glad this is working well for you for now! I wish I had more time to write you something a bit more granular, but unfortunately time isn't on my side right now 😞