Find value in cell from list of values (does cell contain anything in this list) – Google Docs

named range google docs sheets

One of the most useful formulas for me was being able to find if a cell contained a value from a list of items, this excellent Excel formula https://exceljet.net/formula/cell-contains-one-of-many-things was exactly what I needed.

The formula: =SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0 also works in Google docs, provided that you created a named range.

Here is the spreadsheet for you to look at:

Feel free to poke around the spreadsheet too! 

Important note: I also included an example of where my named range contained a blank cell, don’t do this – if you do this, the entire formula falls apart and you’ll get bad values.

To create a named range in Google docs, simply highlight your cells and click on the Data menu bar and select Named Ranges:

named range google docs sheets

Then name it appropriately:

named_range_name

How to: Accessing the new Google Docs Spreadsheets

google drive google docs spreadsheets

The new Google Docs spreadsheets are awesome in so many ways, but you might not have access! Sometimes you’ll get a little pop up on the bottom right hand corner, but I didn’t. I couldn’t access the new sheets if my life depended on it, until I started digging around.

If you don’t have access yet (you’ll know if you do because you’ll see the new Add-on menu in the top menu), this is what you need to do:

Step 1: Open up Drive and click on the gear icon top right and click on settings

google drive settings gear icon box

Step 2: Click on the Edit Settings tab and then click on Try the new Google Sheets checkbox. Voila.

newgooglespreadsheetsactivation

That should be it. If you were one of the unfortunate ones scratching your head, this should fix it for you.

Count characters in a cell without spaces – Excel & Google docs

By using =Len() you’re going to return the character count including spaces:

Example:

 = Len(a1) 

 where a1 = “hey there”

This formula will return “9”, this is because there’s a space between “hey” and “there”.

Here’s the formula to get character count without spaces:

=LEN(SUBSTITUTE(a1," ",""))

This will return: 8

There you go. This will work in both Excel and Google docs spreadsheets 🙂

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