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

Thomas Farray
edited 12/09/19 in Archived 2015 Posts

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

This discussion has been closed.