Functions that are easier in Google spreadsheets vs Excel

google drive google docs spreadsheets
There are quite a few functions I find much easier to use in Google docs, which I plan on detailing in this ongoing post. If you know of any cool / easier to use functions in Google docs vs Excel, please leave them in the commments and I’ll add them to the list.

Let’s get going shall we?

1) CONCATENATE – Combining several cells into one cell

Concatenate in Excel for several cells is painful, you have to manually select each cell while holding CTRL to grab a set. It’s okay for a few cells, but unless you plan on developing Carpal Tunnel syndome, I’d recommend using Google docs for this.

Have a play around in the sheet to see what’s going on. Easy right? You can even modify the contents of each cell before concatenate, like I did in the example above using spaces. Cool!

2) De-Duplicating using UNIQUE()

In Excel, the easiest way to de-duplicate is using the “remove duplicates” button in the Data ribbon, but I find it far easier to control this in Google docs using the Unique() function.

Have a play around in the sheet to see what’s going on.

3) Translation – No equivalent in Excel

Yes, you saw that right. Google spreadsheets come with the handy Googletranslate() function which allows you to bulk translate text from one language to another using the Google Translation service. The function itself needs three arguments : (input text, language from, language to). But but I don’t know what language I’m translating from!? No problem, Google docs has a cool function that can do that for you too, it’s called Detectlanguage().

Have a play around in the sheet to see what’s going on.

Still to come….

  • More fun with arrayformula()
  • Splitting, concatenation, transposing
  • COUNTUNIQUE
  • SORT
  • FILTER

If you know of any other functions / methods you find easier to use in Google docs, please share them in the comments or contact me directly so I can add it to the post.  I’ll happily link to you, so please contribute 🙂

Bulk ImportXml tool & source (Google docs spreadsheets)

google drive google docs spreadsheets

There’s been a few of you requesting a way to bypass the 50 importxml limit in Google docs so I’ve decided to release something publicly.

Click here to view the spreadsheet

Just make sure to sign in, then make a copy, then press the run button once to authorize the script. If the script doesn’t run, or isn’t there, see the section below.

How does it work?

Please keep in mind I AM NOT A PROGRAMMER, but I do ensure that my code works properly – so please be constructive with your feedback 🙂

The only way I could do this efficiently was to use a script to set up the ImportXml formula in the sheet. This means that I was never able to call importxml with the Sheet class, setFormula method and then replace the formula fast enough. Even if I did manage to copyvalues and clear the importxml formula from the cell, it would either timeout, result in errors or very rarely…work.

Another fun issue was that Google docs would store the results for importxml in cache, but would display N/A# when I ran through the first loop. WTF. Ok, so add in another loop and now it’s displaying the right results. Don’t ask, I have no idea, but it works.

The script isn’t authorizing, or it’s not there!

Yep, that can happen – here is the source code.

function bulkXml() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var Num = Browser.inputBox("How many URLs do you need to scrape?");

  for (y=0;y<2;y++) {

    for (x=2;x-2 < Num;x++)  {

      var url = sheet.getRange(x,1).getValue();
      sheet.getRange(2,6).setValue(url);
      var xpathResult = sheet.getRange(3,6).getValue();    
      var counter = x -1;
      sheet.getRange("C4").setValue(" PLEASE WAIT...CURRENTLY FETCHING " + counter + " OUT OF " + Num);

      if (y===1){
        sheet.getRange(x,2).setValue(xpathResult);
        sheet.getRange("C4").setValue("PROCESSED " + counter + " OUT OF " + Num);
        SpreadsheetApp.flush();
      }

    }

  }

}

function clear() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange("a2:b1000").setValue("");

}

Click on Tools > Script editor and copy paste into there. Make sure you save the script and then you should be good to go.

When I click on the button nothing happens!

I’ve assigned scripts to the buttons, but they sometimes get lost when you make a copy of the Google doc

Right click on the Run button, in the top right you’ll see a drop down arrow. Select assign script, then enter: bulkXml

Will the canonical tag remove a page from the index?

beyonce-funny

If I use rel=canonical on a page that points to this page, can I find the canonicalized page in the index?

Hypothesis:

Setting a canonical target from page A to page B will remove page A from the index.

Result:

Nope, it’s not visible in the index – at least I can’t find it with these advanced operators. So I’m going to conclude that using canonical removes the page from the index.

Oh, and just in case you’re going to say Googlebot didn’t crawl the page, well here’s a request 🙂

66.249.75.72 - - [04/Feb/2013:22:31:32 -0600] "GET /canonicalized-page-testing-relcanonical/ HTTP/1.1" 200 19941 "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"