I have a powershell script currently that pulls in data from a Google Sheet on a team drive and then creates Active Directory users in our environment based on that data. I haven't gotten to fully test this part yet, but don't believe it will cause me any problems.
Where I am having issues is I would like to use a Team-Shared Google Drive Document to contain all of my logging for this automation.
I have had no issues authenticating but cannot seem to get the POST request correct for batchUpdating the document itself. I am able to obtain the revision Id without any problem. I cannot seem to find out where to pass the "batchUpdate" object in my request. I am not worried about where the text is stored on the page once it is added, just looking to add the text to the file.
Documentation: https://developers.google.com/docs/api/reference/rest/v1/documents/batchUpdate#http-request
Here is how I am obtaining my access token
function Get-Auth{
#Obtains access token for the Google API's
#Runs everytime to ensure there is a fresh access token available to the service account
$refresh_body = @{
client_id='338139966542-qaa4me7l4fs0l1ltl2e6kjidjr9tf3up.apps.googleusercontent.com';
client_secret='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';
refresh_token='1//04GjkX_q30ioyCgYIARAAGAQSNwF-L9IrKggdZo-Pq9E_t3qEbmhHmQe8JVVDW4vaXGoeFiRxxJ5KRu7f7TpQVDsHrwMdmc7Xqn4';
grant_type="refresh_token";
}
#Makes a request to obtain the access token
$refresh_token = Invoke-RestMethod -Uri "https://www.googleapis.com/oauth2/v4/token" -Method POST -Body $refresh_body
$access_token = $refresh_token.access_token
}
Here is where I am attempting to update the document
$document = Invoke-RestMethod -Headers @{Authorization = "Bearer $access_token"} -ContentType "application/json" -Method GET -Uri "https://docs.googleapis.com/v1/documents/1K8_q_VCTWin_s8aVb0D16DNujqA72eravHojMfM9cyo"
$revId = $document.revisionId
$updateObject = {
requests = [
{
insertText= {
text= "The Red Dog Crosses the Road";
endOfSegmentLocation= {
segmentId= ""
}
}
}
]
writeControl= {
requiredRevisionId= $revId;
}
}
Invoke-RestMethod -Headers @{Authorization = "Bearer $access_token"} -ContentType "application/json" -Method POST -Uri "https://docs.googleapis.com/v1/documents/1K8_q_VCTWin_s8aVb0D16DNujqA72eravHojMfM9cyo:batchUpdate" -Body $updateObject
Here is the error I receive when trying to make this request.
Invoke-RestMethod : The remote server returned an error: (400) Bad Request.
At line:39 char:1
Invoke-RestMethod -Headers @{Authorization = "Bearer $access_token"} ...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebExceptio
n
FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
I think I just need to find the correct place to put my batchUpdate object in the request, but I could be completely off. Any help would be much appreciated.
CodePudding user response:
In your script, how about the following modification? I thought that the reason of your issue is due to the value of $updateObject
and the request body of -Body $updateObject
. In this case, the request body is required to be the string value. I thought that this might be the reason of your issue.
Modified script:
$document = Invoke-RestMethod -Headers @{Authorization = "Bearer $access_token"} -Method GET -Uri "https://docs.googleapis.com/v1/documents/1K8_q_VCTWin_s8aVb0D16DNujqA72eravHojMfM9cyo"
$revId = $document.revisionId
$updateObject = @{
requests = @(
@{
insertText = @{
text = "The Red Dog Crosses the Road";
endOfSegmentLocation = @{
segmentId = "";
}
}
}
)
writeControl = @{
requiredRevisionId = $revId;
}
}
Invoke-RestMethod -Headers @{Authorization = "Bearer $access_token"} -ContentType "application/json" -Method POST -Uri "https://docs.googleapis.com/v1/documents/1K8_q_VCTWin_s8aVb0D16DNujqA72eravHojMfM9cyo:batchUpdate" -Body (ConvertTo-Json -Depth 4 $updateObject)
Note:
- When this script is run, I confirmed that the text of "The Red Dog Crosses the Road" is appended to the Google Document.