Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
SmartSheet Powershell Module proposal
Hello,
After having spent some time on this, I am just wishing to share this to anyone willing to use the powershell module I have written.
I did this on top of my work, and I might not have time to update or bring correction, but I use this module for my own work. It uses .NET framework and I run with powershell V2. I did not try with other versions.
This module is meant to modify an existing sheet, not to create new one.
It does not handle Columns neither. those have to be built manually
Getting started :
Download the script, and store it anywhere with the .psm1 Extention
Launch powershell, and import-module [FQDN of your file]
Get a SmartSheet API token
Store it using the Set-SmartSheetAPIToken command (the token will be Encrypted, and can not be read by any other account than your account. It uses Windows DataProtection API)
You are now ready to use this module. I have added inline help, so you can use get-help [command name] anytime.
Here are the new commands :
- - Add-SmartsheetRow
- - Get-Smartsheet
- - Get-SmartSheetAPIToken
- - Get-SmartsheetRow
- - Remove-SmartsheetRow
- - Set-SmartSheetAPIToken
- - Update-SmartsheetRow
Please note that all properties starting with __ are used as "Smartsheet system values"
Using this module :
* Load a SmartSheet
$SmartSheet = get-smartsheet "API TEST"
* Read the sheet :
$SmartSheet.table
* Read a row
$Row = $SmartSheet.Table[6]
* See if a row has childs
$Row.__childnode
* See if a row has a parent
$Row.__parentnode
* modify a row (2 methods)
$Row.Name = "New Name"
$Row.update()
Update-SmartsheetRow $Row
You can also choose to modify a Single column :
$Row.update("name")
Update-SmartsheetRow $Row -Column "name"
4) copy a row (2 methods)
$DestRow.AddRow($SourceRow)
Add-SmartsheetRow -Placement siblingId -ParentRow $DestRow -NewRow SS $SourceRow
5) Copy a Row as a child row (2 methods)
$DestRow.AddChild($SourceRow)
Add-SmartsheetRow -Placement parentId -ParentRow $DestRow -NewRow SS $SourceRow
6) Create a new row at the top
- Add-SmartsheetRow -SS $SmartSheet -Placement totop -newrow (new-object psobject -property @{ name = "Name" ; country = "France" ; Gender = "Male"})
7) Delete a row and all childs ( 2 methods) :
$Row.delete($true)
Remove-SmartsheetRow -row $row -Recurse
8) Delete a row (2 methods)
$Row.delete()
Remove-SmartsheetRow -row $row
9) Generate a LeafTable
This a remanipulating a Talbe, to show only leafs but with parent data included ... please check by your own :
$SmartSheet.GenerateLeafTable()
$SmartSheet.LeafTable > To access it
10) Get ServerInfo (for help on colors and formating) :
$SmartSheet.GetServerInfo()
$SmartSheet.ServerInfo
11 ) formating a row :
It is possible to set colors and formating, please check smartsheet API section regarding this.
To add colors & formating :
add-member -InputObject $Row -MemberType noteproperty -Name __format = @{}
$Row.__format.add("name",",,1,1,,,,,,,,,,,,")
$Row.update
This will add Bold & italic to the cell in the column "name" of the specified row
to add this formating, you will have to .add on every column
############################### CODE ##########################
# SmartSheet Version 1.0
# Developped by Thomas Farray .:|:. Cisco .:|:.
Function Get-SmartSheetAPIToken {
<#
.Synopsis
Tool used to retrieve a smartsheet API token and store it into a PSCredential object. The file is encrypted and may only be read by the user account which created it. This script will consider that you are using the same Smartsheet username than your actual session username. if not, please specify with the -username switch
.Description
The Get-SmartSheetAPIToken go fetch credentials that are stored into a file in the user's profile directory. It returns a System.Management.Automation.PSCredential object. The file is encrypted using the Windows Data Protection API (DPAPI) standard string representation
.Example
$SSToken = get-SmartSheetAPIToken
If no credentials were previously set, this command will prompt for a user/passowrd. In either case, it will return a System.Management.Automation.PSCredential object
.Example
$SSToken = get-SmartSheetAPIToken -user mySmartSheetUserName
If no credentials are set, Prompts for a passowrd and returns a System.Management.Automation.PSCredential object
Optional : The name of the credential's account
.Parameter pwdfile
Optional : file FQDN to store the hash
Default : The file is stored in you profile in "\appdata\\Roaming\[login].pwd"
.Parameter pwdfile
Optional : file FQDN to store the hash
Default : The file is stored in you profile in "\appdata\\Roaming\[login].pwd"
.Inputs
null
.OutPuts
[PSCredential]
#>
[cmdletbinding()]
param(
[string]$pwdfile = $env:APPDATA + "\" + ($env:username).split(".")[0] + "_SSToken.pwd",
[string]$user = ($env:username).split(".")[0]
)
process {
# Getting Credentials either from file or from prompt
if (test-path $pwdfile) {
$SecureString = Get-Content $pwdfile | convertto-securestring
} else {
$SecureString = Read-Host Please enter your SmartSheet API Token for the account ($user) -AsSecureString
}
$Credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $user, $SecureString
write-verbose "SmartSheet token read for $user"
return $Credential
}
}
Export-ModuleMember -Function Get-SmartSheetAPIToken
Function Set-SmartSheetAPIToken {
<#
.Synopsis
Tool used to store SmartSheet API token in the users's profile directory. Created credentials may only be read by the user who created them
.Description
The Set-SmartSheetAPIToken is meant to store a SmartSheet API Token to an encrypted file.
.Example
Set-SmartSheetAPIToken
Will prompt for a passowrd for the username of the actual session. It then stores the credentials into a hashed file called in your profile
.Example
Set-SmartSheetAPIToken
Prompts for a password, and stores the credentials into a hashed file.
.Example
Set-SmartSheetAPIToken -user MySmartSheetUserAccount
Prompts for a password for MySmartSheetUserAccount, and stores the credentials into a hashed file.
.Parameter user
Optional : The name of the smartsheet account account
.Parameter pwdfile
Optional : file FQDN to store the hash
Default : The file is stored in you profile in "\appdata\Roaming\[login].pwd"
.Inputs
[int]
.OutPuts
[bool]
#>
param(
[string]$pwdfile = $env:APPDATA + "\" + ($env:username).split(".")[0] + "_SSToken.pwd",
[string]$user = ($env:username).split(".")[0]
)
process {
# Getting Credentials either from file or from prompt
write-verbose ("login : $username, file : $pwdfile, validation = $NoValidation")
$SecureString = Read-Host Please enter your password for the account ($user) -AsSecureString
$Credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $user, $SecureString
$SecureString | ConvertFrom-SecureString | out-file $pwdfile
write-host Crendentials stored to $pwdfile -foregroundcolor darkgreen
}
}
Export-ModuleMember -Function Set-SmartSheetAPIToken
function Invoke-Smartsheet {
[cmdletbinding()]
Param(
$SS,
[string]$SSFuntion,
[string]$SSMainFuntion = "sheet/",
[string]$SSData,
[string]$Method,
[switch]$Format
)
if (!$global:SSWebClient) {
$global:SSWebClient = new-object System.Net.WebClient
$null = [System.Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$global:SSser = New-Object System.Web.Script.Serialization.JavaScriptSerializer
}
if (!$SS) {
$URL = "https://api.smartsheet.com/1.1/sheets"
$SSToken = (Get-SmartSheetAPIToken).GetNetworkCredential().password
} elseif ($SSMainFuntion -match "serverinfo") {
$URL = "https://api.smartsheet.com/1.1/" + $SSMainFuntion + "/" + $SSfuntion
} else {
$URL = "https://api.smartsheet.com/1.1/" + $SSMainFuntion + $SS.ID + "/" + $SSfuntion
$SSToken = $SS.ssToken.GetNetworkCredential().password
}
$global:SSWebClient.Headers.Clear()
$global:SSWebClient.Headers.add("Authorization","Bearer $SSToken")
if ($Format) { $URL += "?include=format" }
if ($Method -like "delete") {
$Jason = $global:SSWebClient.UploadString($URL, $Method, $null)
} elseif ($Method) {
if (!$SSData) { write-error " Impossible to add Data, no Data was imput" ; return }
$Jason = $global:SSWebClient.UploadString($URL, $Method, $SSData)
} else {
$Jason = $global:SSWebClient.DownloadString($URL)
}
$RESULT = $SSser.DeserializeObject($Jason)
if ($RESULT.resultCode) { if ($RESULT.resultCode -ne 0 ) { write-error "Invoke-Smartsheet : Commmand failed with error : $($RESULT.message)" } }
Return $RESULT
}
# Export-ModuleMember -Function Invoke-Smartsheet
function ConvertTo-JsonSmartSheetRow {
param(
[Parameter(Mandatory=$true)]$ROW,
$SS
)
process {
if (!$SS) { $SS = $ROW.__smartsheet }
$AllValidCol = @($ROW.psobject.properties | ? { $_.name -notlike "__*" } | select -expandproperty name)
if (!$SS) { write-error "please add a -SS pointing to a smartsheet to convert this object" ; return $Null}
if (!$AllValidCol) { write-error "ConvertTo-JsonSmartSheetRow : No Properties were found" ; return $null }
$JasonData = @($AllValidCol | % {
$Value = $ROW.$_
if ($Value -as [int] -or $Value -eq 0) { $Value = "$Value" } else {$Value = """$Value""" }
$LocalJason = "{""columnId"": $($SS.Colname2ID.$_) ,""value"": $Value "
if ($ROW.__format.$_) { $LocalJason += " , ""format"": """ + $ROW.__format.$_ + """" }
$LocalJason + " }"
}) -join ","
if (!$JasonData) { write-error "Not able to convert the given row to Jason" ; return }
$JasonData = "{ ""cells"": [" + $JasonData + "]}"
write-verbose " ConvertTo-JsonSmartSheetRow Jason Data : $JasonData"
return $JasonData
}
}
# Export-ModuleMember -Function ConvertTo-JsonSmartSheetRow
function Clone-SmartSheetRow {
param(
[Parameter(Mandatory=$true)]$ROW
)
process {
$NewROW = $Row.psobject.copy()
$NewRow.psobject.properties | ? { $_.name -match "__" } | % { $NewRow.psobject.properties.remove($_.name) }
return $NewRow
}
}
function Show-SmartsheetError {
param($Result,$action)
$Code = $Result.resultCode
$Message = $Result.message
}
function Update-SmartsheetRow {
[cmdletbinding()]
param(
[Parameter(Position=0,Mandatory=$true)]$row,
[string[]]$Column
)
process {
if ($Column) {
$JasonData = ConvertTo-JsonSmartSheetRow ($row | Select-Object($Column + "__*"))
} else {
$JasonData = ConvertTo-JsonSmartSheetRow $row
}
$Result = Invoke-Smartsheet -SSfuntion "row/$($row.__id)" -method "PUT" -ss $row.__smartsheet -SSData $JasonData
}
}
Export-ModuleMember -Function Update-SmartsheetRow
function Get-SmartsheetRow {
param([Parameter(Mandatory=$true)]$Row,[switch]$Format)
$SS = $row.__smartsheet
if ($Format) {
$SSRawRow = (Invoke-Smartsheet -SSfuntion "row/$($Row.__id)" -ss $SS -Format).cells
} else {
$SSRawRow = (Invoke-Smartsheet -SSfuntion "row/$($Row.__id)" -ss $SS).cells
}
$Properties = @{}
$SSRawRow | % { if ($ss.ID2Colname.($_.columnId)) { $Properties.add($ss.ID2Colname.($_.columnId),$_.value)} }
return new-object PsObject -property $Properties
}
Export-ModuleMember -Function Get-SmartsheetRow
function Add-SmartsheetRow {
<#
.Synopsis
Add a new row to your SmartSheet
.Description
This will allow you to add a new row to you smartsheet
- The smartsheet object ($smartsheet)
- opt. A reference row
- Where to store the row
- The new values to store
.Parameter SS
Mandtory : A smartsheet object
.Parameter Row
Optional : A row of a table of the smartsheetobject
.Parameter Values
Mandatory : A list of columns to update, type Dictionnary
.Example
$MySmartSheet = Get-Smartsheet "VIF B*"
###### TO UPDATE Set-SmartsheetRow -SS $MySmartSheet -Row $MySmartSheet.Table[0] -Column Note -NewValue NewVAL
.Inputs
PsObject,PsObject,string,string
.OutPuts
Nothing, but it will update the variable with the new value
#>
[cmdletbinding()]
param(
[PsObject]$ParentRow,
[Parameter(Mandatory=$true)][PsObject]$NewRow,
[Parameter(Mandatory=$true)][ValidateSet("parentId","siblingId","toTop","toBottom")][String]$Placement="siblingId",
[PsObject]$SS,
[Switch]$ToBottom
)
process {
#Preparing JSON Data
if (!$SS) { if ($ParentRow.__smartsheet) { $SS = $ParentRow.__smartsheet } }
if (!$SS) { if ($NewRow.__smartsheet) { $SS = $NewRow.__smartsheet } }
if (!$SS) { write-error "Unable to find a link to the smartsheet, please use -SS or -ParentRow switch" ; return $null }
switch ($Placement) {
"siblingId" { $JasonData = "{ ""siblingId"" : $($ParentRow.__id) ," }
"ParentId" { $JasonData = "{ ""parentId"" : $($ParentRow.__id) ," }
"toTop" { $JasonData = "{ ""toTop"" :true ," }
"toBottom" { $JasonData = "{ ""toBottom"" :true ," }
}
if ($ToBottom -and $Placement -match "siblingId|ParentId" ) { $JasonData += """""toBottom"""":true, " }
$JasonData += """rows"":[ " + (ConvertTo-JsonSmartSheetRow $NewRow $SS) + " ]}"
# Running the new row addition against smartsheet
write-verbose " Invoke Jason Data : $JasonData"
$Result = Invoke-Smartsheet -SSfuntion "rows" -method "POST" -SSData $JasonData -ss $SS
if ($Result.resultCode -eq 0) {
if ($NewRow.__id) { $NewRow = Clone-SmartSheetRow $NewRow }
switch ($Placement) {
"siblingId" {
write-verbose (" New sibling row added to smartsheet")
if ($ParentRow.__ParentNode) {
add-member -InputObject $NewRow -MemberType noteproperty -Name __ParentNode -Value $ParentRow.__ParentNode
$ParentRow.__ParentNode.__childnode += $NewRow
} else {
$SS.Table += $NewRow
}
}
"ParentId" {
write-verbose (" New child row added to smartsheet")
add-member -InputObject $NewRow -MemberType noteproperty -Name __ParentNode -Value $ParentRow
if ($ParentRow.__ChildNode) {
$ParentRow.__ChildNode += $NewRow
} else {
add-member -InputObject $ParentRow -MemberType noteproperty -Name __ChildNode -Value @($NewRow)
}
}
"toTop" {
write-verbose (" New row at top added to smartsheet")
$SS.Table += $NewRow
}
"toBottom" {
write-verbose (" New row at bottom added to smartsheet")
$SS.Table += $NewRow
}
}
if (!$NewRow.psobject.methods.Item("AddRow")) {
Add-Member -InputObject $NewRow -MemberType ScriptMethod -Name 'Update' -Value $SSUpdateRowBlock
Add-Member -InputObject $NewRow -MemberType ScriptMethod -Name 'AddChild' -Value $SSAddNewChild
Add-Member -InputObject $NewRow -MemberType ScriptMethod -Name 'AddRow' -Value $SSAddNewRow
Add-Member -InputObject $NewRow -MemberType ScriptMethod -Name 'Delete' -Value $SSRemoveRow
}
Add-member -InputObject $NewRow -MemberType noteproperty -Name __id -Value $result.result[0].id
Add-Member -InputObject $NewRow -MemberType noteproperty -Name '__smartsheet' -Value $SS
$SS.Table_ID2PSo.add($NewRow.__id,$NewRow)
$SS.Colname2ID.Keys | % {
if (!$NewRow.psobject.properties.item($_)) {
add-member -InputObject $NewRow -MemberType noteproperty -Name $_ -Value ""
}
}
} else {
write-host (" Unable to add a new row to row ID $($ParentRow.__id) ") -foregroundcolor red
}
}
}
Export-ModuleMember -Function Add-SmartsheetRow
function Remove-SmartsheetRow {
<#
.Synopsis
Removes a row to your SmartSheet
.Description
This will simply remove the sent row
- The smartsheet object ($smartsheet)
- A reference row
.Parameter SS
Mandatory : A smartsheet object
.Parameter Row
Mandatory : Row of the smartsheetobject to remove
.Parameter Recurse
Optional : This will remove all the childs of the row
.Example
$MySmartSheet = Get-Smartsheet "VIF B*"
Remove-SmartsheetRow -SS $MySmartSheet -Row $MySmartSheet.Table[0]
.Inputs
PsObject,PsObject
.OutPuts
Nothing, but it will update the variable with the new value
#>
[cmdletbinding()]
param(
[Parameter(Mandatory=$true)]$Row,
[switch]$Recurse
)
process {
if ($recurse) {
if ($Row.__childnode) { $Row.__childnode | % { Remove-SmartsheetRow -row $_ -Recurse } }
}
$Result = Invoke-Smartsheet -SSfuntion "row/$($Row.__id)" -method "DELETE" -ss $row.__smartsheet
if ($Result.resultCode -eq 0) {
write-verbose " Row ($($row.__id)) has beed deleted"
if ($row.__ParentNode) {
$row.__ParentNode.__childnode = @($row.__ParentNode.__childnode | ? { $_.__id -notlike $row.__id } )
} else {
$row.__smartsheet.table = $row.__smartsheet.table | ? { $_.__id -notlike $row.__id }
}
$row.__smartsheet.Table_ID2PSo.remove($row.__id)
$row = $null
}
}
}
Export-ModuleMember -Function Remove-SmartsheetRow
$SSGenerateLeafTableBlock = {
write-verbose "LeafTable : Building it !"
$LTable = @() ; $LTable_ID2PSo = @{}
foreach ($node in ($this.table | ? {!$_.__Childnode} )) {
# We need to duplicate the object, else we will modify the Table list of objects
$NewObj = $node.psobject.copy()
$NewObj.psobject.properties | ? { $_.name -match "__Childnode|__ParentNode" } | % { $NewObj.psobject.properties.remove($_.name) }
Add-Member -InputObject $NewObj -MemberType NoteProperty -Name '__OriginalRow' -Value $node
$LTable_ID2PSo.add($NewObj.__id,$NewObj)
# Then, we need to update each line with flattended values
$ActualNode = $node
[System.Collections.ArrayList]$MissingProperties = $ActualNode.psobject.properties | ? { $_.name -notmatch "__Childnode|__ParentNode" -and !$_.value } | select -expandproperty name
while ($ActualNode.__ParentNode -and $MissingProperties) {
$ActualNode = $ActualNode.__ParentNode
$ToRemove = @($MissingProperties | % { if ($ActualNode.$_) { $NewObj.$_ = $ActualNode.$_ ; ($_) } })
$ToRemove | % { $MissingProperties.remove($_)} # can not remove an item while enumerating
}
$LTable += $NewObj
}
Add-Member -InputObject $this -MemberType NoteProperty -Name 'LeafTable' -Value $LTable
Add-Member -InputObject $this -MemberType NoteProperty -Name 'LTable_ID2PSo' -Value $LTable_ID2PSo
}
$SSUpdateRowBlock = {
param([string[]]$Parameters2update)
if ($Parameters2update) {
Update-SmartsheetRow -row $this -Column ($Parameters2update)
} else { Update-SmartsheetRow -row $this }
}
$SSAddNewChild = {
param([PsObject]$NewNode,[bool]$ToBottom=$false)
if ($ToBottom) {
Add-SmartsheetRow -parentRow $this -NewRow $NewNode -ToBottom -Placement parentId
} else {
Add-SmartsheetRow -parentRow $this -NewRow $NewNode -Placement parentId
}
}
$SSAddNewRow = {
param($NewNode)
$Result = Add-SmartsheetRow -parentRow $this -NewRow $NewNode -Placement siblingId
}
$SSGenerateServerInfo = {
Add-Member -InputObject $this -MemberType Noteproperty ServerInfo (invoke-smartsheet -SSMainFuntion "serverinfo" -SS $this)
}
$SSRemoveRow = {
param($recurse = $false)
if ($recurse) {
Remove-SmartsheetRow -row $this -recurse
} else {
Remove-SmartsheetRow -row $this
}
}
function Get-Smartsheet {
<#
.Synopsis
This function is meant to fetch a SmartSheet and return a PSobject that will represent your SmartSheet
.Description
1 - Pre-requise
Before beeing able to use this tool, you will need to create a SmartSheet API token.
To get this token, please go to http://Smartsheet.cisco.com
Then > Account (top left) > Personal settings > API Access > Generate a token`n
You will be requested the token when launching the tool. You can also store it using the Set-SmartSheetAPIToken SmartSheet
2 - If multiple replies
You will get a list of all the seets. In case of mutiple similar names, please specify with -ID switch
3 - When a Single SmartSheet is selected, you will get a new object
Example : $MySmartSheet = get-smartsheet -ID 012345678910
This object represent the smartsheet, here are the base sub-objects
ID : the ID of the SpreadSheet
SStoken : A Secured variable used to store your SmartSheet API token
ID2Colname : A dictionnary of ID / Columns name
Colname2ID : A dictionnary of Columns name / ID
Table : The represented SmartSheet. This is what you will use almost all the time
Table_ID2PSo : A way to directly go from a Row ID to the Row Object (no need to filter)
To this smartsheet Object, you may run additional commands (ex : $MySmartSheet.GetServerInfo())
GenerateLeafTable : Will generate a new table that only contains leaf object, but that will copy (if not not empty) all data of the parent. It will generate 2 variables : $MySmartSheet.LeafTable & $MySmartSheet.LTable_ID2PSo - equivalent to the previous one for Leaf Table
GetServerInfo : Will retrieve the server info (which holds, for example, color shemes - please check API manual) and store it to a new variable $MySmartSheet.ServerInfo
.Parameter name
Optional : Name of the smartsheet. Wildcards accepted
.Parameter ID
Optional : ID of the smartsheet. Strict ID requested
.Example
You must specify the name or the [ID] of the smartsheet (-name or -ID), below are sheets corresponding to your filter to :
> API [12312312312312]
> Another Test API [12312312312312]
...
.Example
$APITest = Get-Smartsheet "API"
$APITest.Table
(Get-Smartsheet API).table[0]
__id : 2610625433102212
Nom : AZER
Prenom : Thomas
__SmartSheet : @{ID=12312312312312; SStoken=System.Management.Autom...}
.Inputs
String,Long
.OutPuts
Array of psobject
#>
[cmdletbinding()]
param(
[String]$name,[long]$ID
)
process {
# Part 1 : We go fetch the correct smartsheet
if (!$ID) {
$AllSS = Invoke-Smartsheet | % { new-object PSCustomObject -Property $_ }
if ($name) { $AllSS = $AllSS | ? { $_.name -like $name } }
if ($AllSS.count) {
write-host "You must specify the name or the [ID] of the smartsheet (-name or -ID), below are sheets corresponding to your filter to : " -foregroundcolor red
$AllSS | % { write-host " > $($_.name) [$($_.id)]" -foregroundcolor darkgreen }
return $null
}
if (!$AllSS) {
write-host "No sheet was found using the search string : $name" -foregroundcolor red
return $null
}
$ID = $AllSS.id
}
# Part 2 : We now have to ID of the sheet to work on
$SSCred = Get-SmartSheetAPIToken
$SSDeseralised = Invoke-Smartsheet -ss (new-object psobject -property @{ID = $ID ; ssToken = $SSCred})
if (!$SSDeseralised) {
write-error ("No sheet was found with " + (if ($name) { "name : $name "}) + "ID [$ID]")
return $null
}
# Working on Colums
$Col_ID_2_Names = @{} ; $Col_Names_2_ID = @{} ; $RowProperties = @{}
$SSDeseralised.columns | ? {!$_.systemColumnType -and ($_.title -notlike "Last Edit Date")} | % { $Col_ID_2_Names.add($_.id,$_.title) ; $Col_Names_2_ID.add($_.title,$_.id) }
$Col_ID_2_Names.Values | % { $RowProperties.add($_,"") }
$Result = New-object psobject -property @{ Colname2ID = $Col_Names_2_ID ; ID2Colname = $Col_ID_2_Names ; SStoken =$sscred ; ID = $SSDeseralised.id }
$RowProperties.add("__SmartSheet",$Result) # adding a revert pointer to the smartsheet
$RowProperties.add("__id",$null)
write-verbose ("Colums found : " + ($RowProperties.keys -join ", "))
# Tranforming to Table
$Table = @() ; $Table_ID2PSo = @{}
write-verbose "Table : Building the main table"
$SSDeseralised.rows | % {
$TempObj = New-Object psobject -Property $RowProperties
$TempObj.__id = $_.id
$Table_ID2PSo.add($_.id,$TempObj)
# Transforming Cells to Data
$_.cells | % {
$Colname = $Col_ID_2_Names.item($_.columnId)
if ($Colname) { if($_.value -as [INT]) { $TempObj."$ColName" = [INT]$_.value } else { $TempObj."$ColName" = $_.value } }
}
# Linking parents & childs
if ($_.parentId) {
$Parent = $Table_ID2PSo.($_.parentId)
add-member -inputobject $TempObj noteproperty -name "__ParentNode" -value $Parent
if ($Parent.__Childnode) { $Parent.__Childnode += $TempObj
@} else { add-member -inputobject $Parent noteproperty -name "__Childnode" -value @($TempObj)
}
}
Add-Member -InputObject $TempObj -MemberType ScriptMethod -Name 'Update' -Value $SSUpdateRowBlock #-PassThru
Add-Member -InputObject $TempObj -MemberType ScriptMethod -Name 'AddChild' -Value $SSAddNewChild #-PassThru
Add-Member -InputObject $TempObj -MemberType ScriptMethod -Name 'AddRow' -Value $SSAddNewRow #-PassThru
Add-Member -InputObject $TempObj -MemberType ScriptMethod -Name 'Delete' -Value $SSRemoveRow #-PassThru
$Table += $TempObj
}
Add-Member -InputObject $Result -MemberType Noteproperty Table $Table
Add-Member -InputObject $Result -MemberType Noteproperty Table_ID2PSo $Table_ID2PSO
Add-Member -InputObject $Result -MemberType ScriptMethod -Name 'GenerateLeafTable' -Value $SSGenerateLeafTableBlock
Add-Member -InputObject $Result -MemberType ScriptMethod -Name 'GetServerInfo' -Value $SSGenerateServerInfo
#-PassThru
return $Result
}
}
Export-ModuleMember -Function Get-Smartsheet
Comments
-
Thanks for sharing your Powershell module. I am just learning Powershell, but I am very excited by the possibility of using Powershell with Smartsheet. Please keep us updated on your progress. - See more at: https://community.smartsheet.com/discussion/smartsheet-powershell-module-proposal#sthash.usALMw6q.dpuf
-
This is awesome. Shame I didn't know about it until now. I'd love to see this on github where others can more easily find it and contribute. Let me know if I can help.
-
I have updated it since last time. This was using the old API.
Please send me a mail if you want the version I actually use.
I won't have time to managed that in Github, but feel free to post where you want
Thomas
