“Sync” button greyed out on SharePoint library

If the “Sync” option is not available for a library on SharePoint, it could be a couple different things.

Note: Media and Form/InfoPath Libraries will not sync but you can still open them in Windows Explorer and pin to Quick Access so they will function as a mapped drive would.

Sync button is available in one library but no another on the same site

Go to the library for which the sync button is disabled

Go to library settings

Click “Advanced Settings”

Make sure “Offline Client Availability” is set to “Yes”

Sync button is not available on ANY library on a site

Go to Site Settings and select “Search and offline availability” from the “Search” section

Make sure “Offline Client Availability” is set to “Yes”

Content and script editor web parts missing in SharePoint

Missing two of your favorite web parts? Don’t fret.

Make sure the site collection feature “SharePoint Server Publishing Infrastructure” is activated

Make sure the site feature “SharePoint Server Publishing” is activated

Still not there? Check “Settings” in the admin center to see if custom scripts are allowed on personal and self-service created sites (allow both). This can take up to 24 hours to take effect.

If you can’t wait (and you’re using SharePoint Online), use PowerShell to make the change immediately, changing my site details to your own

$adminUPN="nate@sharepointlibrarian.com"
$orgName="sharepointlibrarian"
$UserCredential = Get-Credential -UserName $adminUPN -Message "Enter password"
Connect-SPOService -URL https://sharepointlibrarian-admin.sharepoint.com -Credential $userCredential
Set-SPOsite https://sharepointlibrarian.sharepoint.com -DenyAddAndCustomizePages 0

Repeat the last line for each site on which you need to quickly activate this setting. For example, I would run it again on https://sharepointlibrarian1.sharepoint.com/sites/AnotherSite

Your web parts should have now returned! Good luck.

Microsoft Surface Pro 4 “plugged in, not charging”

Alas, I reached a day where I thought I was done with my Surface. It powered on when plugged in just fine but was at 0% battery and would, of course, instantly shut off when disconnected from AC power. When I hovered over the battery indicator in the task bar, it told me it was “plugged in, not charging.” The nerve!

After some troubleshooting and trying:

  • Making sure devices, particularly under “Batteries”, were all updated
  • Uninstalling and reinstalling power related devices via Device manager
  • Running anti-virus checks
  • Installing latest Windows updates

I found out that if I plugged my surface dock into the surface “upside down” it worked fine and charged as usual. So, long story short, if your Surface isn’t charging just try connecting the power source in the opposite direction.

Also, once it “remembers how to charge itself,” you can reverse this and put the charger in the normal direction (just double-check that it still says charging). Think of it as a quick “refresh” of the surface power connector.

SharePoint calendar all-day events showing as previous day in content search web parts

Despite your regional settings being correct, all-day events for some reason are using UTC time when they’re stored and are likely showing as the wrong day in content search web parts and similar web parts.

Though they look correct as an individual item or on a calendar, the way they’re stored doesn’t acknowledge your regional settings and, when pulled through a search web part, render in UTC as beginning 6 (or other) hours earlier than they actually do.

For example, I have a content search web part that pulls all events in our organization and show’s “today’s events.” If there’s an all day event, it shows as starting 6:00 PM the day prior to its actual day.

I could not find a straightforward solution to fix all affected events. And my solution is not ideal, but it accomplishes a need. You could instead explore the possibility of creating a calculated column that adds hours to fix the alignment. But please share if you’ve encountered the same issue and have resolved it a better way.

To get around this issue, I created a SharePoint Designer 2010 Workflow on calendars that:

  • Triggers on creation or modification
  • “Unchecks” the All Day Event box
  • Sets a specific start time on the start date (I chose 7:00AM)
  • Sets End Time to the original end time (if you don’t do this, it sets end time to blank).

Solution: “This web site has been configured to disallow page editing with SharePoint Designer.”

There are three possible causes I’m aware of that you should check if you receive this message:

  • Central admin settings not configured properly
  • Site collection settings not configured properly
  • You’re using a Project Web App (PWA) site template and can only fix this on SharePoint Server

I’ll cover the solutions for each in the same order:

Central admin

Go to central admin –> manage web applications

Select the web app on which you received the error and select “SharePoint Designer” from the general settings drop-down. Make sure the first box is checked and click “OK.”

Site collection settings

Go to site settings –> SharePoint Designer Settings (under Site Collection Administration)

Make sure “Enable SharePoint Designer” is checked and click OK

Project web app template issue

Log in to a SharePoint server and go to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\TEMPLATE\SiteTemplates\PWA\XML

Open the ONET XML file in a text editor like NotePad

Search for “webdesign” (Ctrl+F to open search) and delete the following property:

DisableWebDesignFeatures="wdfeditpages"

Perform an IIS reset (run SharePoint management shell as administrator)

You may need to repeat these steps on multiple servers if you have multiple web front end servers. You can just copy the ONET file and overwrite the same file on the other servers in the same location. Don’t forget to do an IIS reset afterward on each.

Close and re-open your Project Web App site in SharePoint Designer and you should now be able to edit as you do with other sites.

Quick tip: Prevent #DIV/0! and #VALUE! errors in Excel files

Recently I was helping a client visualize their excel data using Power BI and ran across an error in Power BI when it couldn’t deal with “#DIV/0!” as a cell value in Excel:

“DataFormat.Error: Invalid cell value ‘#DIV/0!’.” 

You get this error in excel whenever you have a calculated formula that is trying to divide by zero or a blank cell. Similarly, you get “DataFormat.Error: Invalid cell value ‘#VALUE!'” typically when your data formats don’t match – like there’s a word in a number-formatted column.

It’s a simple fix: just modify your Excel formula to be wrapped in =IFERROR((YourFormula),0) so that if your formula attempts to divide a value by zero, it returns a value of zero instead of an error code.

=IFERROR(Your_Formula, Value_If_Error)

2018-10-10_09-12-39.gif

Solution: Excel drag to “fill” not working – value is copied, formula ignored

A client of mine recently ran into an issue I hadn’t seen before. When she would click a formula cell and drag down to calculate it across multiple rows, it only copied the value. The formulas were correct, but the value being shown was from the original cell:
2018-09-25_11-29-39.gif

Continue reading “Solution: Excel drag to “fill” not working – value is copied, formula ignored”

How to increase lookup column limit in SharePoint lists

Lookup columns include workflow status columns, traditional lookup columns to other lists, and person/group columns. This includes the two default people fields “Created by” and “Modified by”. If your list has more than 12 of these, you may receive the following error:

“This view cannot be displayed because the number of lookup and workflow status columns it contains exceeds the threshold (12) enforced by the administrator.”

list view threshold.PNGIn SharePoint Online, you’re not able to increase the lookup column limit. Lists created prior to the June 2013 CU update are capped at 8 lookup columns, while those afterward are allowed 12.

However, on-premise SharePoint (server) allows you to change this limit to your heart’s content.

Continue reading “How to increase lookup column limit in SharePoint lists”

Solution: “The user who attempted to complete the task is not the user to whom the task is assigned”

You might run into this issue when running task processes in SharePoint Designer.

“Reason: The user who attempted to complete the task is not the user to whom the task is assigned.”

Assuming the person should actually be able to complete the task, check the following:

  • Make sure the user who was assigned the task does not have multiple accounts. If they do, the task could have been assigned to the account that the person isn’t currently signed in as when attempting to complete the task.
  • If it’s someone completing the task on behalf of another, make sure that individual is either:
    • A Site Owner (site settings, site permissions, people and groups, site owners) or
    • Task process owner (in SPD 2010 workflows, go to the properties for the task step and set task process owner. Click OK and republish workflow.):

Solution: “We’re sorry. We can’t open {file} because we found a problem with its contents.”

When using SharePoint Designer workflows to automatically create documents from a template you might run into an error message:

“We’re sorry. We can’t open {file} because we found a problem with its contents.”

document contents error

Or if you’re just opening it within the browser web app instead of the Word client, you might have seen:

“Sorry, Word Online ran into a problem opening this document. To view this document, please open it in Microsoft Word.”

But if you do open it in Word you then get the first error message. Luckily it’s a simple fix.

Continue reading “Solution: “We’re sorry. We can’t open {file} because we found a problem with its contents.””