Google Spreadsheet – How to put indent to a spreadsheet cell

Today I was working in Google spreadsheet (Google Sheet) for preparing effort estimation for one of the project. Then I realized the need of indentation to move the text of one of the cell to right (to visually show grouping), however I struggle with this for so long but didn’t found any option. After more of exploration on Internet, found a very interesting solution and thought of sharing the same in this blog.

The best solution is to write a script.

To do this open up the spreadsheet and select menu item “Tools” –> “Script Editor”
Once the script editor opens copy and paste the following code into the “Code.gs” window:

var ss = SpreadsheetApp.getActiveSpreadsheet();

function indentText() {
  var values = ss.getActiveRange().getValues();
  var newValues = new Array();
 
  for (i = 0; i < values.length; i++) {
    if (values[i][0] != ”) {
      newValues.push([‘=CONCAT(REPT( CHAR( 160 ), 5),”‘ + values[i][0] + ‘”)’]);
    } else {
      newValues.push([”]);  
    }
  }  
  ss.getActiveRange().setValues(newValues);  
};function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : “Indent Text”,
    functionName : “indentText”
  }];
  sheet.addMenu(“Indent Text”, entries);
};

Go to menu “File” –> “Save”

Return to your spreadsheet and reload the browser.  You should now see a new menu item called “Indent Text” to the right of the “Help” menu.

What this code does is add a menu to the spreadsheet document called “Indent Text”.
Within this menu it will create a menu item called “Indent Text”.
When the “indent Text” menu item is selected any cells within a single column selection will be indented by 5 spaces.

You can increase or decrease the number of spaces the text is indented by changing the line below so that the “5” is changed to a larger or smaller number (depending on your preference)

newValues.push([‘=CONCAT(REPT( CHAR( 160 ), 5),”‘ + values[i][0] + ‘”)’]);

Hope people find this useful. 🙂

Restore items from the SharePoint 2013 Recycle Bin

Interestingly, An item was deleted from a list (Task List) by a user, and to restore that item, users went to Recycle Bin and also to Site Collection Recycle Bin, but no avail. I was told to investigate the date of deletion and by whom.

So, the question asked to me was – Is there any other way to get the details of the deleted item or where the deleted item can be found?

Answer – Yes you can find out the user. Login to the content database, there you will have a table “dbo.RecycleBin”. Query this table for DeleteUserID and then query the table “dbo.UserInfo” with this deleteUserID to get user details. You will get other details as well from this DeleteUserID table.

dbo.RecycleBin:-

 

 

 

 

dbo.UserInfo:-

 

 

 

 

Point to Note – Recycle Bin does not keep deleted items for ever. The default setting in SharePoint is to keep content in Recycle Bin for 30 days after deletion, this can be changed by the SharePoint farm administrators. When this grace period is over, items are moved to the second stage aka Site Collection Recycle Bin.

Two Stages of Recycle bin:
Yes, SharePoint offers Two stages in recycle bins,

First-stage recycle bin – When users delete Files/List items it goes to First stage recycle bin. Also called “End user Recycle bin”. Content in this recycle bin is counted on Site quota, so when you delete a file, it goes to recycle bin, But you wont get any additional free space, still it occupies site quota. This recycle bin is accessible to the end users, and its Security trimmed (Even a site collection admin can’t see end user’s recycle bins, He/She needs to use “Site collection Recycle bin” to see end user’s recycle bin.)

Second-stage recycle bin – When the content deleted from First-stage recycle bin, its moved to Second stage recycle bin. Here Items not counted on Site quota, But total space occupied by second stage recycle bin is based on Central administration setting “Second stage Recycle bin Percentage in Live site’s Quota”. This Recycle bin acts at site collection level, and can be accessed only to site collection admins.

I hope this will help you out.

Anonymous Site Access in Office 365 (SharePoint Online)

Recently, I have been asked a question about Anonymous Access in Office 365 (SharePoint Online). The question was – Can we give anonymous access to users within and outside the organization, so that just like any other Internet facing website, users will not enter their credentials and can directly see the SharePoint site contents ?

The requirement was – To share a Video Library/Document Library with all users (internal & external).

WOW. My first reaction and response to the question was “NOT POSSIBLE”. We can apply anonymous access in on-premise SharePoint Site/List/Library, but not in Office 365. There might be some third-party web-parts or sandbox solutions to deal with this, but there is no direct way to give anonymous access in Office 365.

To confirm my statement, I explored on Internet and found the same answers. We can only share list items with external users but cannot share a site/list anonymously. Also, I was sure that even Public facing sites cannot solve this purpose. Then I explored more on Public facing and Private sites in Office 365 and surprisingly, found the following facts from MS sites which details about Office 365 updates and plans:-

  1. Beginning May 1, 2017, anonymous access for existing sites will no longer be available.
  2. On September 1, 2017, when Microsoft deletes the public site collection in SharePoint Online, customers will no longer have access to the content, images, pages or any other files that reside on their public website. Before September 1, 2017, customers should make a backup copy of all their public website content, images, pages, and files, so they don’t lose them permanently.
  3. On March 31, 2018, Microsoft will delete all public sites that have been postponed.

I have listed only few updates and announcements from MS, the complete information, you can get on this site – Information about changes to the SharePoint Online Public Websites feature in Office 365

Finally, to conclude on anonymous access in Office 365, I will say that you can use external sharing features of SharePoint Online to share content with people outside your organization who do not have licenses for your Microsoft Office 365 subscription. However, that is just a sharing and not anonymous access, which means every user has to enter username and password to access the site resources. Share sites or documents with people outside your organization

Pros and Cons of Office 365

This article will help you to determine whether Office 365 is good or not for your organization, because moving to the Cloud can be a big decision for any business. Below are some of the Pros (advantages) and Cons (disadvantages) of using Office 365.

Pros

  1. Work Anywhere
  2. Email hosted in the cloud
  3. Easy and simple file storage and synching
  4. Easy Collaboration
  5. Always have access to latest products and applications (Always up-to-date)

Cons

  1. Less flexible infrastructure
  2. Subscription based
  3. Data privacy – All your private business data will live on Microsoft’s servers. This might not be a concern for everybody, but for certain use cases it might be even impossible (due to legal reasons) to have your data hosted outside your organization.
  4. If the internet is down, you may not be able to access work.
  5. It can be hard to keep up with changing feature set – The features in Office 365 do change fairly frequently. On one hand, you always have access to the latest and great versions.

How to identify whether a site is based on SharePoint or not

We often come across many sites on Internet or intranet and we always want to know that whether the site is based on SharePoint or not. So, in this article I am going to list all possible ways to identify if a site is based on SharePoint or not.

  • The simplest way is to check the structure of the site i.e. folder structure or different pages. This pattern may show you a SharePoint Publishing site (Pages/Pagename.aspx or default.aspx). You can also search for /SitePages library by typing in the URL.
  • If the site if of high importance OR if the site is very famous, then it will definitely be published as Microsoft case study : http://www.microsoft.com/casestudies
  • As we know, SharePoint on-premise server is hosted on IIS (Internet Information Server), so we can check using HttpResponse or by some other tools that which hosting server is used for a particular site.
  • In Chrome or Firefox browsers, open up console and view JavaScript’s files used on the page. If you see init.js, core.js etc., then it’s definitely a SharePoint site.
  • Right click on any page and see for “view source”. Click on that and search for meta tag. If it’s a SharePoint site, then it will definitely have:-
    <head><meta name=”GENERATOR” content=”Microsoft SharePoint” />
    src=”/_layouts/1033/init.js”
  • If it’s a SharePoint site, then it must be calling web services in it. You can call any of the SharePoint OOTB web services (if it doesn’t open, then you will see the legendary “Error page” or “Something went wrong” page). Just access any of the web service present inside the “_layouts” folder.
    E.g. – <<Site_Url>>/_layouts/lists.asmx

If you will check or test any of the above one or two points, then definitely you can be say that whether site is based on SharePoint or not.

CASE STUDY: –

Interestingly, while browsing the Indian Income Tax Department website (http://incometaxindia.gov.in/pages/default.aspx), I found that this site is based on SharePoint….voila !!

To confirm on this, I tested using the above points and below are my observations: –

1) Clicked on view source on the home page and searched for meta tag: –

it-1-meta-tag

 

 

2) Then I searched for init.js, _layouts based structure and other files related to SharePoint:-

it-2-init-js

 

 

3) Lastly, I checked the internal web service of SharePoint by typing this URL in browser – http://incometaxindia.gov.in/_layouts/lists.asmx , and see below the SharePoint Error page:-

it-3-error-page

 

 

 

Do you still need proof? Well enough, I know its SharePoint site now.

Competitive Exams for school going students (class – 5th to 10th, 11th & 12th class)

Hello,

This blog is something unique and not related to SharePoint. This blog is related to school going students who are preparing or wish to prepare for cometitive examinations. This blog will give you complete information about the various competitive exams. Please click on below links to get the details:-

competitive-exams-for-further-studies-after-12th

engineering-entrance-exam-dates-2017

national-exams-academics

Important Code Snippets

  1. .NET Regex to express positive numbers with decimal
    @”^[0-9]{1,20}(\.[0-9]{1,6})?$”
  2. SharePoint People Picker Control
    <SharePoint:PeopleEditor />
  3. ASP.NET Textbox Textmode=”Number” – RegularExpressionValidator
    <asp:RegularExpressionValidator ID=“RegularExpressionValidator1” runat=“server” ControlToValidate=“tbAccount” ErrorMessage=“Please Enter Only Numbers” ForeColor=“Red” ValidationExpression=“^\d+$”>
  4. ASP.NET Set the value of a textbox textmode=”date”
    txtbox.Text = DateTime.Today.ToString(“yyyy-MM-dd”);