Monday 28 August 2017

Deploying Applications via Cloud Shell

Deploy your application to the production App Engine environment:

 gcloud app deploy ./app.yaml 

After the application is deployed, you can visit it by opening the URL http://[yourappname].appspot.com in your web browser.

To upload your cron jobs, you must specify the cron.yaml as a parameter to the following gcloud command:

 gcloud app deploy cron.yaml

Cron jobs can be checked at https://console.cloud.google.com/appengine/taskqueues

If you get an error with multiple versions (greater than 15)

Get the list of versions:
gcloud app versions list 

Delete versions (multiple separated by space)
gcloud app versions delete [version#1] [version#2] ..etc

Tuesday 11 April 2017

Powershell Excel Function Breakdown

Came across this useful list whilst reading The Underground - this one is a real timesaver as they have compiled it all in one easy to read place.

#####################################################################
# Open New Excel Workbook / WorkSheet
$Excel = new-object -comobject excel.application
$ExcelWordBook = $Excel.Workbooks.Add()
$ExcelWorkSheet = $ExcelWordBook.Worksheets.Add()
$Excel.Visible = $true
 
#####################################################################
## Load Excel  file
$ExcelPath = 'C:\KM_Main.xlsx'
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$ExcelWordBook = $Excel.Workbooks.Open($ExcelPath)
$ExcelWorkSheet = $Excel.WorkSheets.item("Sheet 1")
 
#####################################################################
# Close connections to Excel
# set interactive to false so no save buttons are shown
$Excel.DisplayAlerts = $false
$Excel.ScreenUpdating = $false
$Excel.Visible = $false
$Excel.UserControl = $false
$Excel.Interactive = $false
## save the workbook
$Excel.Save()
## quit the workbook
$Excel.Quit()
## function to close all com objects
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
## close all object references
Release-Ref($ExcelWorkSheet)
Release-Ref($ExcelWordBook)
Release-Ref($Excel)
 
 
#####################################################################
# Change to a different Worksheet
$ExcelWorkSheet = $Excel.WorkSheets.item("Sheet 2")
 
#####################################################################
# Update / Insert / Delete Value in a Cell
$ExcelWorkSheet.Cells.Item(1,1).Value2 = "New Value"
 
#####################################################################
# Read Cell
$ExcelWorkSheet.Cells.Item(1,1).Text
 
#####################################################################
# Delete Row / Column
[void]$ExcelWorkSheet.Cells.Item(1,1).EntireColumn.Delete()
[void]$ExcelWorkSheet.Cells.Item(1,1).EntireRow.Delete()
 
#####################################################################
# Find Last Used Column or Row
$ExcelWorkSheet.UsedRange.columns.count
$ExcelWorkSheet.UsedRange.rows.count
 
#####################################################################
# Sorting
$table = $ExcelWorkSheet.ListObjects | where DisplayName -EQ "User_Table"
$table.Sort.SortFields.clear()
$table.Sort.SortFields.add( $table.Range.Columns.Item(1) )
$table.Sort.apply()
 
#####################################################################
# Clear all formatting on a sheet
$tableRange = $ExcelWorkSheet.UsedRange
$tableRange.ClearFormats()
 
#####################################################################
# Using Excel Table Styles<
$listObject = $ExcelWorkSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $ExcelWorkSheet.UsedRange, $null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null)
$listObject.Name = "User Table"
$listObject.TableStyle = "TableStyleLight10"
 
#####################################################################
# Auto-Sizing Columns / Rows
$ExcelWorkSheet.UsedRange.Columns.Autofit() | Out-Null
 
#####################################################################
# Formatting a Column
$ExcelWorkSheet.columns.item($formatcolNum).NumberFormat = "yyyy/mm/dd"
 
#####################################################################
# Formatting Text / Numbers Colours
 
#####################################################################
# Format Text / Numbers Bold
$ExcelWorkSheet.Cells.Item(1,1).Font.Bold=$True
 
#####################################################################
# Add Hyperlink to cell
$r = $ExcelWorkSheet.Range("A2")
[void]$ExcelWorkSheet.Hyperlinks.Add($r, $link)
 
#####################################################################
# Add Comment to Cell
$ExcelWorkSheet.Range("D2").AddComment("Autor Name: `rThis is my comment")
 
#####################################################################
# Add a Picture to a Comment
$image = "C:\test\Pictures\Kittys\gotyou.jpg"
$ExcelWorkSheet.Range("C1").AddComment()
$ExcelWorkSheet.Range("d3").Comment.Shape.Fill.UserPicture($image)
 
#####################################################################
# Fix Location and Size of comment
$ExcelWorkSheet.Range("D3").Comment.Shape.Left = 100
$ExcelWorkSheet.Range("D3").Comment.Shape.Top = 100
$ExcelWorkSheet.Range("D3").Comment.Shape.Width = 100
$ExcelWorkSheet.Range("D3").Comment.Shape.Height = 100
 
#####################################################################
# Making a Comment/s visible
$comments = $ExcelWorkSheet.comments
foreach ($c in $comments) {
$c.Visible = 1
}
 
#####################################################################
# Add a Formula
$formula = "=8*8"
$ExcelWorkSheet.Cells.Item(1,1).Formula = $formula