[Quora]| How to get google search results in google sheets/ excel

Here are steps to get google search results in google sheets or excel

Create a google sheet in your google drive.
Create a google custom search from here.
Enable search the entire web.
Copy your google custom search engine id.
Get your google custom search api key from here
Formulate your api key in this format.

https://www.googleapis.com/customsearch/v1?key={YOUR_API_KEY}&cx={CUSTOM_SEARCH_ENGINE_ID}&q={KEYWORD}

1

https://www.googleapis.com/customsearch/v1?key={YOUR_API_KEY}&cx={CUSTOM_SEARCH_ENGINE_ID}&q={KEYWORD}

key is your API key, cx is your google custom search id.

In your google sheets, go to tools-> script editor-> paste the following code->save the file as ImportJSON.gs

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;
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

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;

}

Enter the cx, key, search query (q), API URL in different cells.
Use the concatenate function to join all these 4 variables to get your final api url. For example

=concatenate(D4,D2,"&cx=",D1,"&q=",D3)

1

=concatenate(D4,D2,"&cx=",D1,"&q=",D3)
Use the ImportJSON function to import the google search results. for example to get the title of the search results

=ImportJSON($D$5,"/items/title","noHeaders")

1

=ImportJSON($D$5,"/items/title","noHeaders")
Here are the search results in google sheets and also a link to the sheet where you can see the search in work.

How to get google autocomplete inside google sheets or excel

6 Comments

Meera Sanghavi · April 29, 2019 at 6:33 am

Hi,

This is a very valuable piece of information. Thank you for it. However I am not able to implement it. Initially I got ‘Loading’ message for very long which I thought would be because there are too many results and second time I tried then I am getting reference error.

To give more details, I am basically trying to extract details like Name, Phone, Number and address from Google search or from specific website such as http://www.grotal.com into my excel/google sheet when I enter a query such as Karate Classes in Navi Mumbai. Can you please help me out here?

Muthali Ganesh · April 30, 2019 at 2:03 am

What you are looking to do is scraping and is not related to this article

Matej · April 30, 2019 at 7:09 pm

Thanks, your instructions are clear and solution works great. What I am still trying to figure out is where (in importJSON.gs) do I limit the number of results to 1?

Muthali Ganesh · May 1, 2019 at 3:40 pm

Hello Matej, Try using &num=1 in the google custom search api

Ludo · May 20, 2019 at 12:51 pm

Hi, thanx for info. To make it work for me I had to substitute “,” for “;” in all formulas. It works now, but I get only 10 results. Is there a way to show 100 lines of results? Thanx for answer.

Muthali Ganesh · May 23, 2019 at 1:33 pm

I currently dont know an answer to this. I think google gives a max of 10 results for every query. Try using the num function

How to get google search results in google sheets/ excel

Published by Muthali Ganesh on January 10, 2019January 10, 2019

Articles you may like

Muthali Ganesh

6 Comments

Meera Sanghavi · April 29, 2019 at 6:33 am

Muthali Ganesh · April 30, 2019 at 2:03 am

Matej · April 30, 2019 at 7:09 pm

Muthali Ganesh · May 1, 2019 at 3:40 pm

Ludo · May 20, 2019 at 12:51 pm

Muthali Ganesh · May 23, 2019 at 1:33 pm

Leave a Reply Cancel reply