Friday, May 31, 2019

(fixed) Import-Module: The specified module was not loaded because no valid module file was found in any module directory

I tried to load module 'CredentialManager' through the script below:

Import-Module CredentialManager -DisableNameChecking

And got error:

Import-Module: The specified module "CredentialManager" was not loaded because no valid module file was found in any module directory

Then I ran "Get-InstalledModule" to check it.

No problem, the module was installed. Then why I cannot import it?

$env:PSModulePath shows:

C:\Users\username\AppData\Local\Apps\SharePointPnPPowerShellOnline;C:\Program Files\WindowsPowerShell\Modules;C:\WINDOWS\system32\WindowsPowerShell
\v1.0\Modules;C:\Program Files\SharePoint Online Management Shell\

Emmm...."C:\Users\username\Documents\WindowsPowerShell\Modules
C:\Program Files\WindowsPowerShell\Modules" is not there.

I can fix the problem by adding this path to PSModulePath environment variable. But, I believe the better way is to re-install "CredentialManager".

Uninstall-Module CredentialManager
Install-Module CredentialManager

After the re-installation, this module appeared in "C:\Program Files\WindowsPowerShell\Modules"

Great!

Thursday, May 30, 2019

When to use folder in SharePoint list/library?

Just read a nice article. I agree with Joanne Klein, but I only see two practical reasons to use folder.

1. Permission control.
2. Too many items in one list/library.

So, if there is choices, go for metadata!

Office 365 is too complicated!

At the moment, I can see 23 Apps under Office 365.

How can we train thousands of users to understand when to use which one?

In my opinion, we only need three apps, and Dynamics 365 should not be part of Office 365.

1. Synchronous communication: Online chatting service(Teams)
2. Asynchronous communication: Email(Outlook)
3. Information management: SharePoint

Most of those apps should join SharePoint. They are:

Calendar,Delve, Excel, Flow, Forms, OneDrive, OneNote, People, Planner, PowerBI, PowerApps, PowerPoint, Stream, Sway, Tasks, To-Do, Video, Word, Yammer

So, why it is so complicated? For more subscription fees?

https://www.office.com/apps?auth=2&home=1



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()