Modify Google Sheets (API) Using PowerShell / Uploading CSV Files

PowerShell Generated Google Sheet
PowerShell Generated Google Sheet

I recently had a need to interact with a Google Sheets spreadsheet and it turned out to be pretty easy and worked very well! I wanted to write this post to share what I learned and hopefully it can help some others out there.

In this guide we’ll explore how to create a Google API key then and use PowerShell to create a new spreadsheets and upload a CSV file into Google Sheets.

Acquiring Google Sheets API

First head to the official Google Developers Console. If you have never used the Google API before go ahead and create a new project. I called mine Scripts-jamesachambers.

After creating a project it should take you to the main dashboard. From here we are going to click “ENABLE APIS AND SERVICES” at the top of the page. It looks like this:

Google Sheets Dashboard - Enable APIs and Services
Google Sheets Dashboard – Enable APIs and Services

This will bring up a page showing all the different Google APIs available. From here we are going to scroll down to the G-Suite section and click on “Google Sheets” and then choose the “Enable” button.

Google Developer Console - Add API Menu
Google Developer Console – Add API Menu
Google Sheets Enable Button
Google Sheets – API Enable Button

Enable Google Drive API

Now enable the Google Drive API using the exact same steps. This will be needed for some access/permission controls later.

To enable the Google Drive API follow the same steps we did in the “Acquiring Google Sheets API” but choose “Google Drive” from the G-Suite options instead.

Creating a Service Account

There is a lot of security built into Google’s APIs. Some types of them require user consent but can access private user data. The kind of account we want to create to work with Google Sheets using scripts is a service account.

This type of account uses a certificate to validate your identity instead of a username/password. It is able to create new spreadsheets and give permissions for others to view them without requiring user consent since the service account will own the spreadsheet sidestepping the private user data issue. It is also able to update these sheets right from PowerShell!

When you clicked the “Enable” button it should have taken you into the “Google Sheets API Console” but sometimes I’ve noticed the button just turns into “Manage”. Click Manage of you aren’t already in the Google Sheets developer console. You should be at a screen that looks like this:

Google Sheets API Credentials
Google Sheets API – Credentials Tab

Choose the “Credentials” tab on the left hand of the screen as shown above and click “Create Credentials” and choose the “Service Account” option.

Choose a name for your account. You can set an optional description here as well if you would like. Click the “Create” button and it will take you to the next screen.

Google Sheets API - Service Account Roles
Google Sheets API – Service Account Roles

Give your service account the “Project” – “Owner” role. This will give your service account full access to all resources in the project you created. You can create finer grained permissions if desired but the service account is only going to have access to the project you created so it should be safe unless you have other resources in the project.

Press the button again and leave the final screen on the defaults. Your service account is now created!

Create Service Account Access Key/Certificate

Now that we have created our service account we need to create an access key and download the certificate for it.

At the official Google Developers Console dashboard choose the “Service Accounts” tab from the left side of the screen and then click on the new service account you created:

Google Sheets - API Service Accounts Menu
Google API Service Accounts Menu

Make a note or copy of your service account’s email address listed here. This will be the $iss credential we’ll be using in PowerShell to authenticate with Google very shortly.

At the bottom of your “Service account details” page will be an option to add keys. Click the “Add Key” button and choose “Create new key” as shown here:

Google Sheets - Create API Key
Google API Service Account Details – Create New Key

Choose the “P12” format:

Google Service Account - Create P12 Key
Google Service Account – Create P12 Key

Press “Create”:

Google Sheets - Download API Key
Google Service Account – Private Key Download

A download dialog will pop up and the screen above will be displayed. This is your certificate file your scripts will use to access Google Sheets.

Warning: This is the only time Google will give you this key. If you lose it you cannot recover it or redownload it and have to create a new key and update all your scripts that use that key accordingly. Make sure you have this key backed up!

Once you have safely backed up this key we are all done with configuration and it’s time to head to PowerShell!

Install UMN-Google PowerShell Module

We are going to use the UMN-Google PowerShell module to be able to communicate with Google Sheet’s API. You will want to install this module on any machine you will be running scripts. Open PowerShell and type:

Install-Module UMN-Google

This PowerShell module is developed and maintained by the University of Minnesota. You can see more information at the PowerShell Gallery here: The UMN-Google PowerShellGallery Repository

Getting Authorization Token

This block will appear at the top of your scripts. It will use our service account certificate to get an authorization code from Google.

Create a new PowerShell .ps1 file with the editor of your choice. Copy and paste the code block below:

Import-Module UMN-Google

# Set security protocol to TLS 1.2 to avoid TLS errors
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Google API Authozation
$scope = "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file"
$certPath = "C:/Users/james/Desktop/PowerShell/jamesachambers-1551564735346.p12"
$iss = 'scripts@jamesachambers-1551564735346.iam.gserviceaccount.com'
$certPswd = 'notasecret'
try {
    $accessToken = Get-GOAuthTokenService -scope $scope -certPath $certPath -certPswd $certPswd -iss $iss
} catch {
    $err = $_.Exception
    $err | Select-Object -Property *
    "Response: "
    $err.Response
}

$accessToken

Modify the values below to match your certificate file you downloaded in the previous step and your $iss (service account email address) information that we made a note of/copied earlier. The 2 lines you need to change to your own values are listed in bold.

I recommend fully qualifying the path to your certificate file to avoid working path problems (especially when doing background/automation scheduled tasks). Make sure you remember to copy the certificate from your downloads folder to the location where you created your PowerShell .ps1 file and that you change the path to yours.

After you have modified the values run the script to make sure you don’t receive any errors. If everything ran successfully you will see a valid Google API authorization token! It should look like this:

Testing Google Sheets
PowerShell – Google API Authorization Test

If your screen shows a long string of numbers/letters/symbols and does not give an error then congratulations, you’ve completed your API key setup successfully! If you received an error double check that you copied the certificate and gave the right path and run through the earlier steps quickly and verify everything looks correct.

Create New Google Sheet

Now that you have working authentication it’s time for the fun part! Now we get to start creating/modifying Google Sheet documents. Let’s create a new sheet and display the spreadsheet ID.

Add the following commands to the end of your .ps1 file with the title of your choice:

# Create new spreadsheet
$Title = 'Patching Spreadsheet'
$SpreadsheetID = (New-GSheetSpreadSheet -accessToken $accessToken -title $Title).spreadsheetId
$SpreadsheetID

# Create new sheet
$Sheet = 'Computers'
Add-GSheetSheet -accessToken $accessToken -sheetName $Sheet -spreadSheetID $SpreadsheetID

Now run your .ps1 file again:

Power Shell Google Sheets New Sheet
PowerShell – Google Sheets New Spreadsheet

The first line is your access token again, but the second line is the new spreadsheet ID! To access this spreadsheet you append the spreadsheet ID on the end of the following base URL:

https://docs.google.com/spreadsheets/d/

So for example, to access the spreadsheet I just created in this example go to https://docs.google.com/spreadsheets/d/1anI7Bdr0ZjkSDwMbGpCb0KZc1SpSC-3JdQiycvoM5CM/

However, when you go to the URL you will see:

Google Sheets - You Need Access
Google Sheets – You Need Access

This is because by default these sheets are private. We need to set the permissions for other people to be able to access our new sheet!

Setting Google Sheet Permissions

Let’s start by adding individual users. Comment out the lines that create a new spreadsheet and sheet and add the following lines to the end of the file:

$SpreadsheetID = '1anI7Bdr0ZjkSDwMbGpCb0KZc1SpSC-3JdQiycvoM5CM'
Set-GFilePermissions -accessToken $accessToken -fileID $SpreadsheetID -role writer -type user -emailAddress 'youremail@yourdomain.com'

Change the $SpreadsheetID to the ID you received in the last step and the email to match your own personal email address. This will add “writer” permissions to your account to the spreadsheet. The bolded lines are things you need to adjust. The final result should look like this:

Import-Module UMN-Google

# Set security protocol to TLS 1.2 to avoid TLS errors
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Google API Authozation
$scope = "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file"
$certPath = "C:/Users/james/Desktop/PowerShell/yourcertificate.p12"
$iss = 'scripts@jamesachambers-1551564735346.iam.gserviceaccount.com'
$certPswd = 'notasecret'
try {
    $accessToken = Get-GOAuthTokenService -scope $scope -certPath $certPath -certPswd $certPswd -iss $iss
} catch {
    $err = $_.Exception
    $err | Select-Object -Property *
    "Response: "
    $err.Response
}

$SpreadsheetID = '1anI7Bdr0ZjkSDwMbGpCb0KZc1SpSC-3JdQiycvoM5CM'

Set-GFilePermissions -accessToken $accessToken -fileID $SpreadsheetID -role writer -type user -emailAddress 'youremail@yourdomain.com'

Now run the script.

If you received the following error:

Invoke-RestMethod : The remote server returned an error: (403) Forbidden.

It means you very likely did not enable the “Google Drive” API. Go back to the “Enable Google Drive API” step from earlier and double check that the Google Drive API got enabled!

Now go back to your browser and refresh the “Access Denied” page. You should now see the blank spreadsheet!

Uploading a CSV File to Google Sheets

Now that you’ve set up your Google Sheets API keys and environment we are ready to modify new Google Sheets! If you already have a CSV file to upload you can use that, otherwise let’s create a quick testing CSV. Open Notepad or your favorite editor up to a new document and paste the following text:

Computer,"Operating System",RAM
"iMac 27""",OSX,"16 GB"
"HP ZBook 15""",Windows 10,"16 GB"

Now save the document as test.csv in the same directory you have our “Setting Google Sheets Permissions” PowerShell script from the last step. Remove or comment out the very last Set-GFilePermissions line of the script.

Google Sheets expects an ArrayList of values to upload to Google Sheets. We are going to load our test CSV and convert it to an ArrayList and submit the data to Google Sheets with the following code:

# Import CSV
$import = New-Object System.Collections.ArrayList($null)

# Build sheet header as it isn't included automatically
$import.Add( @("Computer", "Operating System", "RAM")) | Out-Null

# Build ArrayList
$inputCsv = Import-Csv "test.csv"
$inputCsv | ForEach-Object { 
    $import.Add( @($_.Computer, $_."Operating System", $_.RAM)) | Out-Null
}

# Upload CSV data to Google Sheets with Set-GSheetData
try {
    Set-GSheetData -accessToken $accessToken -rangeA1 "A1:C$($import.Count)" -sheetName "Computers" -spreadSheetID $SpreadsheetID -values $import -Debug -Verbose
} catch {
    $err = $_.Exception
    $err | Select-Object -Property *
    "Response: "
    $err.Response
}

This code loads the CSV into a variable called $inputCsv and then iterates through each object and adds it to the ArrayList we are going to send to Google Sheets. If you wanted to modify the data before sending it you absolutely can.

Once the CSV is imported you will have an object array and the names of the variables will be the name of the header of that column. You have to be careful if it has a quote in it. You can still access them by quoting the variable. For example:

$_."Operating System"

This will allow you to access the variables even if there are spaces in the column’s header. The actual first line of the CSV with the headers in it is consumed by PowerShell when you import it so we also create our own header line for our spreadsheet.

Your final code should look like this:

Import-Module UMN-Google

# Set security protocol to TLS 1.2 to avoid TLS errors
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Google API Authozation
$scope = "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file"
$certPath = "C:/Users/james/Desktop/PowerShell/yourcertificate.p12"
$iss = 'scripts@jamesachambers-1551564735346.iam.gserviceaccount.com'
$certPswd = 'notasecret'
try {
    $accessToken = Get-GOAuthTokenService -scope $scope -certPath $certPath -certPswd $certPswd -iss $iss
} catch {
    $err = $_.Exception
    $err | Select-Object -Property *
    "Response: "
    $err.Response
}

# Spreadsheet ID # of the Google Sheet
$SpreadsheetID = '1anI7Bdr0ZjkSDwMbGpCb0KZc1SpSC-3JdQiycvoM5CM'

# Import CSV and build ArrayList
$import = New-Object System.Collections.ArrayList($null)
$inputCsv = Import-Csv "test.csv"
$inputCsv | ForEach-Object { 
    $import.Add( @($_.Computer, $_.'Operating System', $_.RAM)) | Out-Null
}

# Build sheet header as it isn't included automatically
$import.Add( @("Computer", "Operating System", "RAM")) | Out-Null

# Build ArrayList

# Upload CSV data to Google Sheets with Set-GSheetData
try {
    Set-GSheetData -accessToken $accessToken -rangeA1 "A1:C$($import.Count)" -sheetName "Computers" -spreadSheetID $SpreadsheetID -values $import -Debug -Verbose
} catch {
    $err = $_.Exception
    $err | Select-Object -Property *
    "Response: "
    $err.Response
}

Now we’re ready to run the code. Execute your script in PowerShell:

PowerShell - Test of uploading CSV to Google Sheets
PowerShell – Test of uploading CSV to Google Sheets

If everything was set up you should immediately see the changes in your browser window and it should look like this:

Patching Test Spreadsheet
Patching Test Spreadsheet

Wrapping Up

That’s it! You’ve now set up your Google API console access and can now modify Google Sheets through the power of PowerShell automation.

Make sure you check out the University of Minnesota’s GitHub repository to see any new changes or announcements related to the PowerShell extension. It is open source!

International Language Fix (Required for computers not set to “English” as the default language)

Once this post started gaining some traction I learned that international users (specifically ones that didn’t have “English” set as their default language) were having trouble with authentication. I suspected this was due to language differences because I’ve encountered it before on some of my other scripts (like my Minecraft Bedrock dedicated server setup script) that gained international traction as well and once we tested for it sure enough that was the issue.

Yoan graciously shared the solution in the comments:

Function Using-Culture (
[System.Globalization.CultureInfo]$culture = (throw "USAGE: Using-Culture -Culture culture -Script {scriptblock}"),
[ScriptBlock]$script= (throw "USAGE: Using-Culture -Culture culture -Script {scriptblock}"))
{
    $OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
    trap 
    {
        [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
    }
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
    Invoke-Command $script
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
}

This was found at Microsoft Developer Blogs – Using Culture Culture ScriptBlock and the blog post contains a ton of information about how to use it and what it’s doing!

In a nutshell it lets you wrap your calls to UMN-Google (or your entire script if you wanted) like this:

Using-Culture en-US {$accessToken = Get-GOAuthTokenService -scope $scope -certPath $certPath -certPswd $certPswd -iss $iss}

This is a really easy way to wrap your functions if you are getting errors related to language differences / default headers / etc. and is definitely worth a try if your PC’s default language is not set to English!

guest

78 Comments
Inline Feedbacks
View all comments

Chris
Chris
4 months ago

Hello, Sorry for my level of English, I try to make it short! Can you guide me on how to create a list with the elements of the first column of the google sheet? And also how to delete a specific line from the google sheet? (for example in a foreach loop on a compare-object)

Chris
Chris
3 months ago

Hello, sorry for the late answer, I was on vacation! Thank you for your help. Unfortunately I can’t try it since I have a problem at the beginning with authentication. I can’t find out the reason.

Response:
IsMutuallyAuthenticated : False
Cookies : {}
Headers : {Vary, X-XSS-Protection, X-Frame-Options, X-Content-Type-Options...}
SupportsHeaders : True
ContentLength : -1
ContentEncoding :
ContentType : application/json; charset=UTF-8
CharacterSet : UTF-8
Server : scaffolding on HTTPServer2
LastModified : 09/08/2022 12:15:49
StatusCode : BadRequest
StatusDescription : Bad Request
ProtocolVersion : 1.1
ResponseUri : https://www.googleapis.com/oauth2/v4/token
Method : POST
IsFromCache : False

I created a project, enabled Google Drive and Sheet API, created a service account as owner of project and created the P12 key.

Thank you!

Chris
Chris
3 months ago

Thank you for your quick answer. It did not work at first, since $accesstoken returned empty. I added $global: to $accessToken, so that it can get out of the try scope.

try {
Using-Culture en-US {$global:accessToken = Get-GOAuthTokenService -scope $scope -certPath $certPath -certPswd $certPswd -iss $iss}
} catch {
$err = $_.Exception
$err | Select-Object -Property *
"Response: "
$err.Response
}

Maybe it’s not necessary and I did something wrong from the beginning ?

It seems to work, I managed to create the sheet (did not changing right access yet)

samu
samu
5 months ago

Hi, i am sorry for my bad english but i hope that you will understand me.
in the last section “international language fix” you must wrote this:

$accessToken = Using-Culture en-US {Get-GOAuthTokenService -scope $scope -certPath $certPath -certPswd $certPswd -iss $iss}

Rick
Rick
5 months ago

Hello Mr. Chambers

Using your example, I’m inserting a URL into A1:A1of a Google Sheet. I am also using Google’s IMPORTHTML to load ESPN NFL roster member data.
Because of the way IMPORT-CSV works, I forced a TYPE Change to PSCustomObject by modifying the CSV File to only contain a single URL.

I then used the -Header ‘nflstats’ and -Delimiter (x) Options with IMPORT-CSV to ingest the file.

-Header (to simplify the file versus putting the header within the CSV file)
-Delimiter hack (since I control the input, I used the delimiter to force IMPORTHTML to ignore the comma’s (,) within the URL) – if not, the URL would break after the comma (ravens”,) point in the URL

I also removed the second Object.Add(@(“header”)) for the Sheet Header (Breaks IMPORTHTML)

Well, to make a long story short, everything works.
However, instead of insehttps://jamesachambers.com/wp-admin/admin.php?page=theseoframework-settingsrting the URL ASIS, it places an apostrophe (‘) in front of the equal (=) sign of the URL which prevents the import from taking place.

A1:A1 Contains:
‘=IMPORTHTML("https://www.espn.com/nfl/team/roster/_/name/bal/baltimore-ravens","table",1)

Once the apostrophe (‘) is removed, everything works as expected.

Question:
Do you have any idea (pre or post) on data insert or data filter to remove the apostrophe (‘) on insert?

CSV File(testImport.csv) Contains: As Shown
=IMPORTHTML(“https://www.espn.com/nfl/team/roster/_/name/bal/baltimore-ravens”,”table”,1)

Sincerely thanks for the AWESOME script and explanation!!!

FINAL SCRIPT:
Import-Module UMN-Google

# Set security protocol to TLS 1.2 to avoid TLS errors
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Google API Authozation
$scope = "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file"
$certPath = "C:\PathTo\file.p12"
$iss = 'myaccount.com'
$certPswd = 'notasecret'

try {
$accessToken = Get-GOAuthTokenService -scope $scope -certPath $certPath -certPswd $certPswd -iss $iss
} catch {
$err = $_.Exception
$err | Select-Object -Property *
"Response: "
$err.Response
}

# Spreadsheet ID # of the Google Sheet
$SpreadsheetID = '1wZbvTaDD76okxs7GsYuCjDAR4PJEnaJO2ygNnk8RABU'

# Import CSV and build ArrayList
$import = New-Object System.Collections.ArrayList($null)
$inputCsv = Import-Csv "D:\PatheTo\testImport.csv" -Header 'nflstats' -Delimiter X
$inputCsv | ForEach-Object {
# REMOVED Default Object Values
# $import.Add( @($_.Computer, $_.'Operating System', $_.RAM)) | Out-Null
$import.Add( @($_.nflstats)) | Out-Null
}

# Build sheet header as it isn't included automatically
# REMOVED Default Headers
# $import.Add( @("Computer", "Operating System", "RAM")) | Out-Null
# $import.Add( @("nflstats")) | Out-Null

# Build ArrayList

# Upload CSV data to Google Sheets with Set-GSheetData
try {
# REMOVED Default Sheet Location Range
# Set-GSheetData -accessToken $accessToken -rangeA1 "A1:C$($import.Count)" -sheetName "NFLStatsSpreadSheet" -spreadSheetID $SpreadsheetID -values $import -Debug -Verbose
Set-GSheetData -accessToken $accessToken -rangeA1 "A1:A$($import.Count)" -sheetName "NFLStatsSpreadSheet" -spreadSheetID $SpreadsheetID -values $import -Debug -Verbose
} catch {
$err = $_.Exception
$err | Select-Object -Property *
"Response: "
$err.Response
}

Rick
Rick
5 months ago

Sincerely thank you for freely offering a wealth of help, insight and resources. I’ll dig-in an let you know how it goes!

Nathan
Nathan
7 months ago

Hey James, Thank you so much for this – it is exactly what i am looking for although i am not quite there yet with what I am trying to do. I need to be able to replace a column in an existing google sheets. Not sure how I specify this exactly, any pointers?

Clare
Clare
8 months ago

Hi James, I can’t seem to see any details on how to set single cells. I have a googleSheet that I want to update data for selected cells. I have found the command -cell but can’t work out how to set the value. Any assistance would be most appreciated

Dan
Dan
8 months ago

Hi James,

Forgive me if this is somewhere in the comments but I would like to be able to do this with google sheets that have been “shared” with me from other users. Every time I attempt to set permissions or get data I receive the “Invoke-RestMethod : The remote server returned an error: (404) Not Found.” message. Is there some sort of permissions on the originators end that need to be enabled/disabled? The client does not want to make the sheet “public”, or else I would simply upload the csv file.

Dan
Dan
8 months ago

Thanks for the quick reply! So I’m pretty sure you might have answered it in that the creator of the sheet does not have a service account which I would assume from your reply they would need one? Forgive me if this was obvious as I am fairly new to this.

Dan
Dan
8 months ago

The console error is “drive.permissions.create:100%”, which is the only error I’m seeing. So I’m thinking that the permissions need to be set by the creator of the sheet? I apologize if this is confusing or I’m not understanding correctly. I designed an app for this client to scrape a website and email them some specific data when the site updates and they just asked me to do the same thing for their google sheet, to which I replied “shouldn’t be too hard!” Famous last words haha. By the way, I REALLY appreciate how responsive and helpful you’ve been!

Dan
Dan
8 months ago

Definitely makes sense. I ran the script exactly as you have it and just changed the spreadsheetID, and email and I’m still receiving the 404 error. I have both drive and sheets APIs enabled in my services account as well. I will continue to play with it, luckily they are not dying for this, and I’ll let you know what I come up with! Thank you again for all the help, I really appreciate it!

Dan
Dan
8 months ago

Hey James,

So we just had to have me create a sheet with the services account, essentially copy and paste all the data from the existing sheet, then I used the permissions script you have outlined here to make the client a “writer” as well, and everything is good! Thank you so much for all the help!!!

Matt Ayres
Matt Ayres
10 months ago

Hello…

When I push data, the data is always appended with an apostrophe .. why?

Eg my data is:

1

When I view in Sheets it is:

‘1

How to fix?

Andrew
Andrew
8 months ago

Hi there

I’ve got exactly the same problem…
And i dont know how i could fix that :-/
But thanks anyway for this script

csv looks like this:
12.03.2022,Samstag,09:12,2%

in google tabs it looks like this
12.03.2022 Samstag '09:12 '2%

the ‘ only appears if you click inside the field… on the normal view it doesnt appear.. very strange