get linkedin search results in google sheets

[Linkedin] | How to search for Linkedin Profiles Inside Google sheets/excel

Using the below method, you will be able to search for LinkedIn profiles inside google sheets or excel.

  1. Create a google custom search engine.
  2. Under websites to search, enter www.linkedin.com.
  3. Under restrict pages using schema.org type, mention person schema.[Linkedin] | How to search for Linkedin Profiles Inside Google sheets/excel 1
  4. Copy your search engine ID ( CX id).
  5. Get your google custom search API key from here.
  6. Formulate your API call in the below format.
    https://www.googleapis.com/customsearch/v1?key={YOUR_API_KEY}&cx={CUSTOM_SEARCH_ENGINE_ID}&q={KEYWORD}
  7. Now open google sheets.
  8. Go to Tools-> script editor-> and paste the following code.
    function readRows() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var rows = sheet.getDataRange();
      var numRows = rows.getNumRows();
      var values = rows.getValues();
    
      for (var i = 0; i <= numRows - 1; i++) {
        var row = values[i];
        Logger.log(row);
      }
    };
    
    
    function onOpen() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet();
      var entries = [{
        name : "Read Data",
        functionName : "readRows"
      }];
      sheet.addMenu("Script Center Menu", entries);
    };
    
    
    function ImportJSON(url, query, options) {
      return ImportJSONAdvanced(url, query, options, includeXPath_, defaultTransform_);
    }
    
    
    function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {
      var jsondata = UrlFetchApp.fetch(url);
      var object   = JSON.parse(jsondata.getContentText());
      
      return parseJSONObject_(object, query, options, includeFunc, transformFunc);
    }
    
    
    function URLEncode(value) {
      return encodeURIComponent(value.toString());  
    }
    
    
    function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
      var headers = new Array();
      var data    = new Array();
      
      if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
        query = query.toString().split(",");
      }
      
      if (options) {
        options = options.toString().split(",");
      }
        
      parseData_(headers, data, "", 1, object, query, options, includeFunc);
      parseHeaders_(headers, data);
      transformData_(data, options, transformFunc);
      
      return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
    }
    
    
    function parseData_(headers, data, path, rowIndex, value, query, options, includeFunc) {
      var dataInserted = false;
      
      if (isObject_(value)) {
        for (key in value) {
          if (parseData_(headers, data, path + "/" + key, rowIndex, value[key], query, options, includeFunc)) {
            dataInserted = true; 
          }
        }
      } else if (Array.isArray(value) && isObjectArray_(value)) {
        for (var i = 0; i < value.length; i++) {
          if (parseData_(headers, data, path, rowIndex, value[i], query, options, includeFunc)) {
            dataInserted = true;
            rowIndex++;
          }
        }
      } else if (!includeFunc || includeFunc(query, path, options)) {
        
        if (Array.isArray(value)) {
          value = value.join(); 
        }
        
        
        if (!data[rowIndex]) {
          data[rowIndex] = new Array();
        }
        
        
        if (!headers[path] && headers[path] != 0) {
          headers[path] = Object.keys(headers).length;
        }
        
        
        data[rowIndex][headers[path]] = value;
        dataInserted = true;
      }
      
      return dataInserted;
    }
    
    
    function parseHeaders_(headers, data) {
      data[0] = new Array();
    
      for (key in headers) {
        data[0][headers[key]] = key;
      }
    }
    
    
    function transformData_(data, options, transformFunc) {
      for (var i = 0; i < data.length; i++) {
        for (var j = 0; j < data[i].length; j++) {
          transformFunc(data, i, j, options);
        }
      }
    }
    
    
    function isObject_(test) {
      return Object.prototype.toString.call(test) === '[object Object]';
    }
    
    
    function isObjectArray_(test) {
      for (var i = 0; i < test.length; i++) {
        if (isObject_(test[i])) {
          return true; 
        }
      }  
    
      return false;
    }
    
    
    function includeXPath_(query, path, options) {
      if (!query) {
        return true; 
      } else if (Array.isArray(query)) {
        for (var i = 0; i < query.length; i++) {
          if (applyXPathRule_(query[i], path, options)) {
            return true; 
          }
        }  
      } else {
        return applyXPathRule_(query, path, options);
      }
      
      return false; 
    };
    
    
    function applyXPathRule_(rule, path, options) {
      return path.indexOf(rule) == 0; 
    }
    
    
    function defaultTransform_(data, row, column, options) {
      if (!data[row][column]) {
        if (row < 2 || hasOption_(options, "noInherit")) {
          data[row][column] = "";
        } else {
          data[row][column] = data[row-1][column];
        }
      } 
    
      if (!hasOption_(options, "rawHeaders") && row == 0) {
        if (column == 0 && data[row].length > 1) {
          removeCommonPrefixes_(data, row);  
        }
        
        data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
      }
      
      if (!hasOption_(options, "noTruncate") && data[row][column]) {
        data[row][column] = data[row][column].toString().substr(0, 256);
      }
    
      if (hasOption_(options, "debugLocation")) {
        data[row][column] = "[" + row + "," + column + "]" + data[row][column];
      }
    }
    
    
    function removeCommonPrefixes_(data, row) {
      var matchIndex = data[row][0].length;
    
      for (var i = 1; i < data[row].length; i++) {
        matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);
    
        if (matchIndex == 0) {
          return;
        }
      }
      
      for (var i = 0; i < data[row].length; i++) {
        data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
      }
    }
    
    
    function findEqualityEndpoint_(string1, string2, stopAt) {
      if (!string1 || !string2) {
        return -1; 
      }
      
      var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
      
      for (var i = 0; i < maxEndpoint; i++) {
        if (string1.charAt(i) != string2.charAt(i)) {
          return i;
        }
      }
      
      return maxEndpoint;
    }
      
    
    function toTitleCase_(text) {
      if (text == null) {
        return null;
      }
      
      return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
    }
    
    
    function hasOption_(options, option) {
      return options && options.indexOf(option) >= 0;
    }
  9. Save the file as ImportJSON.gs
  10. Enter the search API key format, search engine ID, API Key,  and search query in different cells.
  11. Use concatenate to combine the above 4 variables to formulate the API call.
    =concatenate(D4,D2,"&cx=",D1,"&q=",D3)
  12. Use the ImportJSON function to get the search results into google sheets. For example to get the image, we can use the following function.
    =ImportJSON($D$5,"/items/pagemap/cse_image/src","noHeaders")
  13. Here is a working google sheet with the Linkedin search results. get linkedin search results in google sheets

Articles you may like

5 thoughts on “[Linkedin] | How to search for Linkedin Profiles Inside Google sheets/excel”

  1. Hey Muthali,

    Thanks so much for this post! Super helpful! Quick question – I noticed the output for this is limited to the first page of search results. Do you know of a way to extend the output of this to all search results within the query?

    Thanks again!

      1. So helpful as is your google search from within google sheets but need to access 2nd page or “next page.” Also, any way to pull data like education and history of employment rather than just current

  2. Hi Muthali, Great post!
    How would I access to the second page of search results?
    Also, how would i search for people based in specific locations? I can type in the location into the search query but it doesnt necessarily that is where they are based, it could be a location they have been to in the past.

    1. So helpful as is your google search from within google sheets but need to access 2nd page or “next page.” Also, any way to pull data like education and history of employment rather than just current?

Leave a Comment

Your email address will not be published. Required fields are marked *