Friday, May 17, 2019

Microsoft Flow - Send Email - You don’t have permissions to send emails on behalf of . Contact to check shared mailbox permissions and gain access.

When sending email from Microsoft Flow, I got the error below:

"You don’t have permissions to send emails on behalf of . Contact  to check shared mailbox permissions and gain access."


Click "Send an email" action and get the detailed error message:

"You are not authorized to send mail on behalf of the specified sending account."


Some people suggests, For the Shared Mailbox, you just need to make sure that you or whoever/whatever account will execute the flow will have Send As permissions to it. Then just set the From parameter of the function to the email address of the shared mailbox

It may work. But the easier option is to leave "From (Send as)" field blank.


Friday, April 12, 2019

Quickly delete list items in batch, through CSOM and PowerShell

"Manage Site Content and Structure" in SharePoint Online had gone. So, we cannot manually delete the items easily, anymore.

If there is less than 5000 items in the list, things are much easier: we can save the list template, then delete the list, and rebuild it through the list template. But, if there are more than 10,000 items, it's time consuming.

Here is a simple solution: Using PowerShell to load all items into an array variable, then delete the items one by one, and then commit the requests in batch.

During my test, to delete 48622 items from one list, it took around 6300 seconds. So it's around 7.7 items per second.

If you know any way to improve the performance, please let me know.

Below is the major part of the PowerShell script.

$i = 0
$iItemId = 0

$web = $ctx.Web
$lists =$web.Lists
$listTarget = $lists.GetByTitle($strListName)
$ctx.Load($web)
$ctx.Load($lists)
$ctx.Load($listTarget)

$ctx.ExecuteQuery()

$viewFields = ""
$caml = "$viewFields1000"
$caml += '
'$position = $null
$allItems = @()

Do{
    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ViewXml = $caml
    $camlQuery.ListItemCollectionPosition = $position

    $listItems = $listTarget.getItems($camlQuery)
    $ctx.Load($listItems)
    $ctx.ExecuteQuery()

    $position = $listItems.ListItemCollectionPosition
    $allItems += $listItems
}
Until($position -eq $null)

if ($allItems.Count -lt 1){
    Write-Host "$(__FILE__), No item needs to be deleted. caml=$caml"
    return 0
}

$iItemCount = $allItems.Count
$progressBarTitle = "$(__FILE__), Scan list '$strListName', iItemCount=$iItemCount, deleteSubFolders=$deleteSubFolders, DaysOfDataToKeep=$DaysOfDataToKeep, ReadOnly=$ReadOnly"
foreach($item in $allItems){
    $i++
    Write-Progress -Activity $progressBarTitle -PercentComplete (($i/$iItemCount)*100) -Status "Working"
 
    $iItemId = $item.Id
    if($item.FileSystemObjectType -eq "File" -or ($item.FileSystemObjectType -eq "Folder" -and $deleteSubFolders)){
        $listTarget.getitembyid($iItemId).Recycle() > $null
    }
    else{
        Write-Host "Invalid FileSystemObjectType, item(FSObjType: $($item.FileSystemObjectType), FileRef: $($item.FieldValues.FileRef))."
        Continue
    }
    if ($i % 1000 -eq 0){
        $ctx.ExecuteQuery()
    }
    Write-Verbose "item(FSObjType: $($item.FieldValues.FSObjType), FileRef: $($item.FieldValues.FileRef)) is deleted."
}
$ctx.ExecuteQuery()

Friday, April 5, 2019

PowerApps - save list item into list folder

We know that PowerApps doesn't support folder in SharePoint list. And Workflow 2013 doesn't support it either. As they are both triggered from remote PowerApps or Workflow server, I believe they both use REST API to communicate with SharePoint Online. And the REST API does not have complete support for folders.

So we have to use workflow to "move" the saved item into folder.

Lucky Workflow 2010 is still there, so we can use it to create list item in folder. However, we cannot create a list item from workflow 2010 instance, if the workflow is triggered by item creation.

The only choice is List Workflow 2013 + Site Workflow 2010.

When item is saved into list root folder by PowerApps form, it triggers the list workflow 2013 instance, which change the primary key field (to avoid the conflict with the forthcoming new item in the folder), then start the site workflow 2010 instance.



Pass ItemId to the site workflow parameter list.


In the site workflow, check to create folder if necessary, then create the item in the folder (specify folder name in the folder field), then delete the original item in the root folder.


The List Workflow 2013 instance will not be triggered if the item is created by workflow 2010 instance.

All these steps will be executed in less than 30 seconds during my test, so users need a little bit patience to see the result in the relevant folder.

Done.




Monday, March 11, 2019

Use Python to migrate all posts from Google+ to BlogSpot (blogger)

Google+ is going to be closed down very soon.

I built some Python 3.72 script to migrate all posts from Google+ to BlogSpot (blogger).

Below is the steps.

1. Download credentials.json from:

https://developers.google.com/blogger/docs/3.0/using

2. Log in https://www.blogger.com,get BlogId from URL

3. Download Google+ backup,then uncompress it to a folder

4. Modify the "BLOGID" in source code, post files and credentials.json file path(highlighted)

5. Run the script

www.blogger.com only allow uploading around 700 posts per day, so we will get "rate Limit Exceeded" exception during the uploading. This script will automatically re-try uploading the file when that happens.

To differentiate these migrated posts to the original posts, I added ' - GooglePlus' to the end of the post title.

Photos are not migrated.

The source code is shared here.

================

import time
import logging
import os
import fnmatch
from html.parser import HTMLParser

from bs4 import BeautifulSoup
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

logging.basicConfig(format='%(asctime)s,%(msecs)d %(levelname)-8s [%(filename)s:%(lineno)d] %(message)s',
                    datefmt='%Y-%m-%d:%H:%M:%S',
                    level=logging.DEBUG)

# FILE_FOLDER = 'C:\\EricFang\\Python3\\'
FILE_FOLDER = '\\\\pwdaddy\\Posts\\'
FILE_CREDENTIALS = 'C:\\EricFang\\Python3\\AddPostsToBlogSpot\\credentials.json'
FILE_PATTERN = '*.html'

BLOGID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'
API_SERVICE_NAME = 'blogger'
API_VERSION = 'v3'

# https://developers.google.com/blogger/docs/3.0/using
# https://developers.google.com/identity/protocols/googlescopes
# SCOPES = ['https://www.googleapis.com/auth/blogger.readonly']
SCOPES = ['https://www.googleapis.com/auth/blogger']

g_appflow = InstalledAppFlow.from_client_secrets_file(
    FILE_CREDENTIALS, SCOPES)
auth_url, _ = g_appflow.authorization_url(prompt='consent')

g_creds = g_appflow.run_local_server()

g_service = build(API_SERVICE_NAME, API_VERSION, credentials=g_creds)

g_count_uploaded = 0


def ReadFile(fileContentText):
    parsed_html = BeautifulSoup(fileContentText, features="html.parser")
    htmlBody = parsed_html.body.find('div', attrs={'class': 'main-content'})
    divData = ''.join(map(str, htmlBody.contents))
    return divData


def addPost(postTitle, postContent):
    postBody = {
        "kind": "blogger#post",
        "id": BLOGID,
        "title": postTitle,
        "content": postContent
    }
    global g_service
    while True:
        try:
            postsInsertAction = g_service.posts().insert(blogId=BLOGID, body=postBody,
                                                         isDraft=False)
            posts = postsInsertAction.execute()
            break
        except Exception as ex:
            logging.info("rateLimitExceeded, wait for 60 seconds......")
            time.sleep(60)

    global g_count_uploaded
    g_count_uploaded = g_count_uploaded + 1
    logging.info(str(g_count_uploaded) + ', post('+postTitle+') is uploaded.')


def getPostTitleList():
    dictReturn = {}
    nextPageToken = None
    global g_service
    postsListAction = g_service.posts().list(blogId=BLOGID, maxResults=20,
                                             fetchBodies=False, pageToken=nextPageToken)
    posts = postsListAction.execute()
    # posts is dict, posts.items is list, posts.items[0] is dict
    while True:
        items = posts['items']
        for item in items:
            itemTitle = item['title']
            dictReturn[itemTitle] = item['id']
            # logging.debug('title='+itemTitle+',id='+item['id'])

        postsListAction = g_service.posts().list(blogId=BLOGID, maxResults=20,
                                                 fetchBodies=False, pageToken=nextPageToken)
        posts = postsListAction.execute()
        if 'nextPageToken' in posts:
            nextPageToken = posts['nextPageToken']
        else:
            break
    return dictReturn


def main():
    postTitleList = getPostTitleList()

    listOfFiles = os.listdir(FILE_FOLDER)
    for fileName in listOfFiles:
        if fnmatch.fnmatch(fileName, FILE_PATTERN) == False:
            logging.info('file ('+fileName+') is not valid. skip.')
            continue

        postTitle = fileName.replace('.html', ' - GooglePlus')
        if postTitle in postTitleList:
            logging.info('post ('+postTitle+') exists. skip.')
            continue

        logging.debug("file name: " + fileName)
        with open(FILE_FOLDER+fileName, mode='r', encoding='UTF-8') as f:
            fileContent = f.read()

        fileContent = ReadFile(fileContentText=fileContent)

        addPost(postTitle=postTitle, postContent=fileContent)

    logging.info('completed!')


if __name__ == '__main__':
    main()

Monday, January 14, 2019

Sorting Array and Hashtable in PowerShell

It's different, but both are quite simple.


  • Array

$array=@()
Get-Service -PipelineVariable Service | %{
$arrayItem = [pscustomobject]@{
DisplayName = $Service.DisplayName
Name = $Service.Name
}
$array += $arrayItem

$array | Sort-Object displayname

"Measure-Command {$array | Sort-Object displayname}" took around 3 milliseconds, for 203 items.

Or,

Get-Service | Sort-Object -Property DisplayName | %{
  [pscustomobject]@{
    DisplayName = $_.DisplayName
    Name = $_.Name
  }


  • Hashtable

$hashtable = @{}
Get-Service -PipelineVariable Service | %{
$hashtable[$Service.Name] = $Service.DisplayName
$hashtable.GetEnumerator() | sort -Property value

"Measure-Command {$hashtable.GetEnumerator() | sort -Property value}" took around 5 milliseconds, for 203 items.


Thursday, January 10, 2019

The quickest way to learn PowerShell as a software developer


PowerShell itself is simple. No much difference from C#.

However, it's really full of Pitfalls. There is always unexpected issues.

Below are the resources of PowerShell pitfalls. Through them, we can learn this language quickly!

 is awesome!!

  • The Poster of the Plethora of PowerShell Pitfalls


https://www.simple-talk.com/sysadmin/powershell/a-plethora-of-powershell-pitfalls/

https://www.red-gate.com/simple-talk/sysadmin/powershell/a-plethora-of-powershell-pitfalls-part-2/

https://www.red-gate.com/simple-talk/sysadmin/powershell/the-poster-of-the-plethora-of-powershell-pitfalls/


  • Practical PowerShell Unit-Testing: Getting Started


https://www.red-gate.com/simple-talk/sysadmin/powershell/practical-powershell-unit-testing-getting-started/



  • The Big Book of PowerShell Gotchas

https://devops-collective-inc.gitbook.io/the-big-book-of-powershell-gotchas/

Monday, January 7, 2019

Resolved - PowerShell script only works in Console, but not in ISE?

I'd like to collect workflow information from SPO. But the script below only works in Console, not in ISE.

$WfServicesManager = New-Object Microsoft.SharePoint.Client.WorkflowServices.WorkflowServicesManager($ctx,$Web)

The error message: Cannot find an overload for "WorkflowServicesManager" and the argument count: "2"

Below is how I did the trouble shooting.

1. Compare the PowerShell environment version information.

$PSVersionTable shows everything is same in Console and ISE.

2. Make sure the class exist in both environment.


$ClassName = "Microsoft.SharePoint.Client.WorkflowServices.WorkflowServicesManager"
if ($ClassName -as [type]) {
"$ClassName is already loaded"
}
else{
"$ClassName is not loaded"
}

Yes, the class is loaded.

3. Check how many relevant assemblies are loaded.


[appdomain]::currentdomain.getassemblies() | ?{$_.CodeBase -match "WorkflowServices"}

Here I noticed that ISE loaded a PnP assembly which also contains "Microsoft.SharePoint.Client.WorkflowServices.WorkflowServicesManager"

Obviously this is the root cause.

I think this is caused by old version PnP, so just removed/uninstalled these assemblies first.

Uninstall-Module SharePointPnPPowerShellOnline -AllVersions -Confirm

Then reinstalled the latest version.

Done.