Use Google Sheets cell content in script

Wouldn't it be nice to collect data using a Google Sheets Form and be able to get each cell value from a script on your phone or computer? It can be done.

I'm assuming the data you need for your script is already in a Google Sheet. It could be collected using a form or similar. To get unauthenticated access to a Google Sheet it needs to be published. Either publish the sheet containing the data or create a and publish new Google Sheet to hold only the data needed for the script. Import the data to that new sheet using =IMPORTRANGE().

To get the URL for the published sheet, have a look at this page: https://developers.google.com/gdata/samples/spreadsheet_sample. Basically you just need the key for the sheet.

The published sheet URL looks something like this:

https://docs.google.com/spreadsheets/d/<key>/pubhtml?gid=0&single=true

After some experimentation with various URL formats I got cell A1 using this URL:
https://spreadsheets.google.com/feeds/cells/<key>/od6/public/basic/R1C1

Cell C2 would look like this:

https://spreadsheets.google.com/feeds/cells/<key>/od6/public/basic/R2C3

OK, on to the content. Below is the content I get when requesting cell R1C1. The actual data is between the tags <content type='text'> and </content>

<?xml version='1.0' encoding='UTF-8'?><entry xmlns='http://www.w3.org/2005/Atom' xmlns:batch='http://schemas.google.com/gdata/batch' xmlns:gs='http://schemas.google.com/spreadsheets/2006'><id>https://spreadsheets.google.com/feeds/cells/<key>/od6/public/basic/R1C1</id><updated>2014-12-12T07:35:19.026Z</updated><category scheme='http://schemas.google.com/spreadsheets/2006' term='http://schemas.google.com/spreadsheets/2006#cell'/><title type='text'>A1</title><content type='text'>2</content><link rel='self' type='application/atom+xml' href='https://spreadsheets.google.com/feeds/cells/<key>/od6/public/basic/R1C1'/></entry>

We just need to split the content on <content type='text'> and then split the second part again on </content>. The cell data is in the first part of the second split.

PowerShell example:
$CellA1 = Invoke-WebRequest -UseBasicParsing -Uri https://spreadsheets.google.com/feeds/cells/1DzK0hnq9bexu6h1ryZzPP6-WWk55IEx0xZrtFKK6UZI/od6/public/basic/R1C1
$Value = (($CellA1 -split "<content type=.text.>") -split "</content")[1]

Tasker example:
GetValueFromSheet (4)
A1: HTTP Get [ Server:Port:https://spreadsheets.google.com/ Path:feeds/cells/<key>/od6/public/basic/R1C1 Attributes: Cookies: User Agent: Timeout:10 Mime Type: Output File: Trust Any Certificate:Off ] 
A2: Variable Split [ Name:%HTTPD Splitter:<content type='text'> Delete Base:On ] 
A3: Variable Split [ Name:%HTTPD2 Splitter:</content> Delete Base:On ] 
A4: Variable Set [ Name:%Value To:%HTTPD21 Do Maths:Off Append:Off ] 

The content of A1 in the Google Sheet is now in Tasker variable %Value.

You could use this to trigger other Tasker tasks or to present that value on a Zooper Widget, or send it to Pushover. I use it for a few things, and might write a more detailed end-to-end post on that.

Change Exchange primary email address to lower case

By default Microsoft Exchange creates email addresses on the form First.Last@domain.com. If you change the email address policy to use lowercase, the existing email addresses are not updated to reflect that change.

It doesn't work to change the primary SMTP address to lower case right away, since email addresses are basically case insensitive. When you set a new primary SMTP address, the old address is kept as an alias email address. Exchange can't set a new primary email address since the same address already exists as an alias. More or less, you get the picture.

I created the following script to make the change

# Get all mailboxes in the users OU (select the OU for which you want to change to lower case
$Mailboxes = Get-Mailbox -OrganizationalUnit "OU=Users,DC=domain,DC=com" -ResultSize Unlimited
# Loop through all mailboxes
ForEach ($Mailbox in $Mailboxes)
{
    # Turn off email address policy
    Set-Mailbox -Identity $Mailbox.Identity -EmailAddressPolicyEnabled $false
    # Set a temporary primary email address
    Set-Mailbox -Identity $Mailbox.Identity -PrimarySmtpAddress "dummy@domain.com"
    # Remove the old primary address which is now a standard email address connected to the mailbox
    Set-Mailbox -Identity $Mailbox.Identity -EmailAddresses @{Remove=$Mailbox.PrimarySmtpAddress}
    # Convert the old primary address to lowercase
    $lowercasesmtp = $Mailbox.PrimarySmtpAddress.ToLower()
    # Set the lowercase email address as primary
    Set-Mailbox -Identity $Mailbox.Identity -PrimarySmtpAddress $lowercasesmtp
    # Remove the temp primary address
    Set-Mailbox -Identity $Mailbox.Identity -EmailAddresses @{Remove='dummy@domain.com'}
    # Turn on the address policy
    Set-Mailbox -Identity $Mailbox.Identity -EmailAddressPolicyEnabled $true
    # Check the result
    Get-Mailbox -Identity $Mailbox.Identity | select Name, *SMTP*, Email*
}