Google Sheets : les fonctions personnalisées (apps scripts) 1


Sommaire

Madeline
Sur twitter

Aujourd’hui, nous continuons la découverte de Google Sheets avec cette fois-ci les fonctions personnalisées. Si vous aviez raté les précédents articles, ils sont ici :

Google Apps + JavaScript = Apps Script

Avec Apps Script, un langage de programmation très proche du JavaScript, vous pouvez créer vos propres fonctions sur Google Sheets. C’est un petit peu le VBA de Google Sheets. On ne verra pas en détail comment fonctionne Apps Script mais juste les codes pour créer des fonctions utiles en SEO. Cela dit, si vous souhaitez profiter du confinement pour apprendre un langage, c’est par ici : https://developers.google.com/apps-script.

Il faut donc coder un peu (ou copier les codes suivants), puis appeler la fonction, comme on appelle n’importe quelle fonction dans Google Sheets.

De manière plus opérationnelle, vous allez dans Outils > Editeur de Scripts :

Editer des scripts pour créer des fonctions personnalisées

Une nouvelle fenêtre s’ouvre, avec le début de code pour créer une fonction « myFunction »

C’est parti !

Il vous suffira de copier/coller les codes suivants en lieu et place de function myFunction() { }.

Importer un fichier JSON

C’était indiqué en teasing dans le précédent article : il n’existe pas de fonction native pour importer un fichier Json. Mais pour pallier à cela, quelqu’un un créé la fonction.

/**Imports JSON data to your spreadsheet 
*Ex: IMPORTJSON("http://myapisite.com","city/population") 
*@param url URL of your JSON data as string 
*@param xpath simplified xpath as string *@customfunction 
*/ 

function IMPORTJSON(url,xpath){
  
  try{
    // /rates/EUR
    var res = UrlFetchApp.fetch(url);
    var content = res.getContentText();
    var json = JSON.parse(content);
    
    var patharray = xpath.split("/");
    //Logger.log(patharray);
    
    for(var i=0;i<patharray.length;i++){
      json = json[patharray[i]];
    }
    
    //Logger.log(typeof(json));
    
    if(typeof(json) === "undefined"){
      return "Node Not Available";
    } else if(typeof(json) === "object"){
      var tempArr = [];
      
      for(var obj in json){
        tempArr.push([obj,json[obj]]);
      }
      return tempArr;
    } else if(typeof(json) !== "object") {
      return json;
    }
  }
  catch(err){
      return "Error getting data";  
  }
  
}

Ceux qui ont l’oeil attentif auront noté que la fonction a 2 arguments : l’url (du fichier json), le xpath. Pour tout savoir sur le fonctionnement de la fonction, voici le lien : http://www.chicagocomputerclasses.com/google-sheets-import-json-importjson-function/ et la démo :

A noter que Matthieu Pinauldt a créé directement un add-on (avec un descriptif en français sur growthhacking) : https://nodatanobusiness.com/importjson/get-started/

Quelques fonctions SEO : récupérer le title, status code, etc.

Vous l’avez vu dans le précédent article, on peut récupérer des éléments d’une page avec la fonction importxml. Par exemple, pour récupérer la balise title, il suffit de faire =importxml(url, « //title »).

Aysun Akarsu avait créé des fonctions pour ne pas s’embêter avec le xpath, et faire par exemple =title(url).

Elle a créé des fonctions pour récupérer le title, le h1, le statuscode, le nombre de liens sortants internes, le nombre de liens sortants externes,…

/* quelques fonctions SEO pour récupérer des éléments intéressants :
* status code (response)
* title
* h1
*...
*https://github.com/aysunakarsu/Simple/blob/master/SEOTools.js
*/

function Response(uri)
{
  try {
  var options = {
  followRedirects : false
 };
  var response = UrlFetchApp.fetch(uri, options);
  return response.getResponseCode() ;  
  }  
   catch (error) {        
     return "Error";
    }
}
function Title(uri) {
  var html = getContents(uri);
  var title = html.match('<title>([^\<]+)</title>')[1];
  return title; 
}
function H1(uri) {
  var html = getContents(uri);
  var h1 = html.match('<h1[^\>]*>([^\<]+)</h1>')[1];
  return h1; 
}
function ILinks(uri) {
  var base_uri = uri.split('://')[0] + "://" + uri.split('://')[1].split('/')[0];
  var inner_links = [];
  inner_links=getInnerlinks(uri,base_uri);
  return inner_links.length; 
}

function getInnerlinks(get_uri,getbase_uri) { 
  try  {
    var html = getContents(get_uri);
    if (html.indexOf('</head>') !== -1 ) {
        html = html.split('</head>')[1];    
        if (html.indexOf('</body>') !== -1 ) { 
           html = html.split('</body>')[0] + '</body>';
           var inner_links_arr= [];
           var linkRegExp = /href="([^\"#]+)"/gi; // regex href 
           var extractLinks = linkRegExp.exec(html);
           var item=getbase_uri;
           var pagesVisited = {};
           while (extractLinks != null) {
              if ((extractLinks[0].indexOf("href=")==0))
                  if (extractLinks[1].indexOf(getbase_uri)==0)  item= extractLinks[1];                 
                  else if (extractLinks[1].indexOf("http")!==0) item= getbase_uri+extractLinks[1]; 
                  if (!(item in  pagesVisited)) {
                    pagesVisited[item]=true;
                    inner_links_arr.push(item);
                    }
              extractLinks = linkRegExp.exec(html); }
           return inner_links_arr;  
           }
       }
  }
  catch (e) { 
   return "Error"
  } 
}
function ELinks(uri) {
  var base_uri = uri.split('://')[0] + "://" + uri.split('://')[1].split('/')[0];
  var external_links = [];
  external_links=getExternalLinks(uri,base_uri);
  if (typeof external_links !== 'undefined' && external_links.length > 0 ) 
      return external_links.length;
 return 0;       
}
function getExternalLinks(get_uri,getbase_uri) { 
  try  {
    var html = getContents(get_uri);
    if (html.indexOf('</head>') !== -1 ) {
        html = html.split('</head>')[1];    
        if (html.indexOf('</body>') !== -1 ) { 
           html = html.split('</body>')[0] + '</body>';
           var external_links_arr= [];
           var linkRegExp = /href="(http[^"#]+)"/gi; // regex href 
           var extractLinks = linkRegExp.exec(html);
           var pagesVisited = {};
           while (extractLinks != null) {
              if (extractLinks[0].indexOf("href=")==0)  {
                  if (extractLinks[1].indexOf(getbase_uri) != 0 &&  (!(extractLinks[1] in  pagesVisited)) ){
                    pagesVisited[extractLinks[1]]=true;
                    external_links_arr.push(extractLinks[1]);}
                    }
              extractLinks = linkRegExp.exec(html); }
           return external_links_arr;  
           }
       }
  }
  catch (e) { 
    return e;
  } 
}
function getContents(uri) {
  var result = UrlFetchApp.fetch(uri); 
  var contents = result.getContentText();
  return contents;
}

Récupérer l’URL finale d’une redirection ?

Si on arrive à récupérer le status code, on obtient une 301, c’est pratique de connaître quelle est l’URL finale. Et bien c’est possible avec cette fonction trouvée sur StackOverflow :

/*
#Test Redirections
#https://stackoverflow.com/questions/27098169/what-google-appsscript-method-is-used-to-get-the-url-of-a-redirect
*/

function getRedirect(url) {
  var response = UrlFetchApp.fetch(url, {'followRedirects': false, 'muteHttpExceptions': false});
  var redirectUrl = response.getHeaders()['Location']; // undefined if no redirect, so...
  var responseCode = response.getResponseCode();
  if (redirectUrl) {                                   // ...if redirected...
    var nextRedirectUrl = getRedirect(redirectUrl);    // ...it calls itself recursively...
    Logger.log(url + " is redirecting to " + redirectUrl + ". (" + responseCode + ")");
    return nextRedirectUrl;
  }
  else {                                               // ...until it's not
    Logger.log(url + " is canonical. (" + responseCode + ")");
    return url;
  }
}  

Tester des redirections

Tout ça, c’est bien… mais the Tech SEO (Jeff Louella) a construit un petit outil de tests de redirections sur Google Sheets. Vous pourrez voir combien il y a de redirections, quels sont les status codes successifs et quelle est l’URL finale.

Certaines fonctions sont un peu redondantes par rapport à celles vues précédemment, mais à vous de tester pour voir celle qui fonctionne le mieux. Voici son code (mais je vous invite à copier directement son google Sheet pour avoir la bonne mise en forme)

/*
*Tester des redirections
*https://www.thetechseo.com/seo-tools/redirect-checker/
*/

function redirectCheck(url, user, pwd) {
  try {
    function getResp(url, user, pwd){  
      var resp = UrlFetchApp.fetch(url, {
        muteHttpExceptions: true,
        followRedirects: false,
        headers: {
          'Authorization': 'Basic ' + Utilities.base64Encode(user+':'+pwd)
        }
      });
      return resp;
    }
    
    var response = getResp(url, user, pwd);
    var rCode = response.getResponseCode();
    var redirectCount = 0;
    var tCode = rCode.toString();
    var location = url;
    var domain = getDomain(url);
    
    while (rCode == 301 || rCode == 302 && redirectCount <= 10) {
      redirectCount++;
      header = response.getHeaders();
      location = getFullUrl(header['Location'],domain);
      domain = getDomain(location);
      Logger.log('location: '+location);
      response = getResp(location, user, pwd);
      rCode = response.getResponseCode(); 
      tCode = tCode + " > " + rCode.toString();
      Utilities.sleep(500);// pause in the loop for 500 milliseconds
    }     
    
    
    Logger.log('redirectCount: '+redirectCount);
    return tCode + "|" + redirectCount + "|" + location;
    
    
  } catch (error) {
    Logger.log(error);
    return "Error| |"+error;
  }
}

function getDomain(url) {
  var domain = '',
      protocol;
  if (url.indexOf("://") > -1) {
    domain = url.split('/')[2];
    protocol = url.split('/')[0];    
    //remove port number
    domain = domain.split(':')[0];
    //add protocol back
    domain = protocol+"//"+domain;
  }  
  
  return domain;
}

function getFullUrl(url,prevDom) {
  var fullUrl,
      domain = getDomain(url);
  if(domain == ''){
    fullUrl = prevDom+url;
  } else {
    fullUrl = url;
  }       
  
  return fullUrl;
}

function redirectCheckTest() {
  var test = redirectCheck('http://blog.pexcard.com/contractors/building-budget-construction-business/');
  Logger.log('test: '+test);
}

Vérifier si une URL est bien indexée

C’est Greenlane qui avait créé un Google Sheet pour vérifier la bonne indexation des URL.

Leur Google Sheet s’appelle Greenlane Indexation Tester v1.6. Il y avait certes quelques faux positifs ou négatifs mais cet outil peut dépanner.

function isIndexed(page,ignoreCase) {
  var urls=[],RETRIES=5;
  
  if(!page)return;
  
  if(page.indexOf("://")!==-1)page=page.split("://")[1].trim();

  var url='https://www.google.com/search?q='+encodeURIComponent(page)+'&fp=1&dpr=2&sns=1&pf=p&tch=1&filter=0';

  if(page.slice(-1)=="/")page=page.slice(0,-1);
    
  try {
    var serp=UrlFetchApp.fetch(url,{muteHttpExceptions:true,method:"GET",followRedirects:true});
    var result=serp.getContentText().split('/*""*/');
    
    var searchResults,serpResults,isIndexed=false;
    
    for(var i=2;i<result.length-1;i++) { //loop through where result might be in various permutations of SERP
      searchResults=JSON.parse(result[i]);
      serpResults=searchResults.d.split('<h3 class="r"><a href="/url?q=');
      isIndexed=findIndexedURL(serpResults, page, ignoreCase);
      if(isIndexed) return "Yes";
    }
    return "No";
    
  } catch(e) { return "No"; }
}

function getSitemap(url) {
  var results=[];
  if(!url)return;
  var sitemap=UrlFetchApp.fetch(url,{muteHttpExceptions:true, method:"GET", followRedirects:true});
  var document = sitemap.getContentText().split("<url>");
  var docHead=document.splice(0,1);
  
  for(var i=0;i<document.length;i++) results.push(document[i].split("</loc>")[0].split("<loc>")[1].replace(/&amp;/g,"&"));
  
  return results;
}

function findIndexedURL(serpResults, page, ignoreCase) {
  for(var i=1;i<serpResults.length;i++) {
    var resultURL=decodeURIComponent(serpResults[i].split('&amp;sa=U')[0]);
    if(resultURL.indexOf("://")!==-1)resultURL=resultURL.split("://")[1];
    if(resultURL.slice(-1)=="/")resultURL=resultURL.slice(0,-1);
    //*debug*/urls.push(resultURL);
    if(ignoreCase=="Yes"){resultURL=resultURL.toLowerCase();page=page.toLowerCase();}//user specified lower case
    if(resultURL==page) return true;
  }
  //*debug*/return urls;
  return false;
}

Combiner les mots clés (et éviter les fonctions ultra warrior)

Dans le 1er article de cette série sur Google Sheets, je montrais comment faire rapidement une liste de mots clés avec des combinaisons. En passant par des fonctions natives, c’était assez horrible. Du coup voici un script qui peut faciliter la chose. Il a été trouvé sur StackOverFlow.

En image, cela donne ca (il faut transposer le tableau avant) :

/* combinaison de cellule (pr remplacer le warrior)
https://stackoverflow.com/questions/48053567/google-spreadsheets-generate-all-combinations-of-4-columns-with-8-rows-each
*/
function combinaisons(arr) {
  return arr.reduce(function(prod, row) {
    var out = [];
    for (i in row) {
      out = out.concat(prod.map(function(x) {
        return x.concat(row[i]);
      }));
    }
    return out;
  }, [[]]).map(function(row) {
    return row.join(" ");
  });
}

Obtenir les données semrush dans google sheet

Pour cela, il faut un abonnement semrush, avec un accès API. Si vous n’avez pas ce prérequis, il n’y aura pas de miracle.

David Sottimano a créé plusieurs fonctions qui permettent de faire directement les appels et récolter les données semrush. Attention cependant lorsque vous fermez/rouvrez votre google sheet…s’il relance des fonctions que vous n’avez pas effacée, on peu très rapidement cramer des crédits (je ne citerai personne… mais j’ai connu ça).

Il y a quelques éléments à modifier : par exemple, vous pouvez changer la base de donnée par défaut par la France (au lieu du Royaume-Uni). Il a vraiment bien commenté ses fonctions et c’est assez facile de s’y retrouver.

/* ---------------------------------------------------------------------------
* Semrush API functions (Google sheets) from Analytics API: https://www.semrush.com/api-analytics/ 
*
* @desc    Access Organic API calls from the Semrush.com API using Google sheets formulas
* @author  Dave Sottimano @dsottimano Twitter
* @license MIT (http://www.opensource.org/licenses/mit-license.php)
* @version 1.0
* -------------------------------------------------------------------------*/

//----------------------------------------------------------------------------

function onOpen() {
  SpreadsheetApp.getUi() 
  .createMenu('Helper Functions')
  .addItem('Check Semrush API balance', 'checkSemrushAccount')
  .addItem('Add your API key', 'addApiKey')
  .addItem('Flush the cache', 'flushAllCacheAndProperties')
  .addItem('Show formula reference', 'formulaReferenceSidebar')
  .addToUi();
  
  if (!SemrushGlobal.data.API_KEY) SpreadsheetApp.getActiveSpreadsheet().toast('In the Helper Functions menu (menu bar), click on Add your API key to get started!', 'The Library is ready to rock :', 5);
    
}

function formulaReferenceSidebar () {
var htmlOutput = HtmlService.createHtmlOutputFromFile('sidebar')
      .setTitle('Semrush API Library (Unofficial)');
   
SpreadsheetApp.getUi().showSidebar(htmlOutput);
}


/* ---------------------------------------------------------------------------*
              SemrushGlobal Object START 
* -------------------------------------------------------------------------*/

var SemrushGlobal = {
    
    data : {
      API_KEY : PropertiesService.getUserProperties().getProperty("semrushkey"),
      DEFAULT_DB : "us",
      API_KEY_PROPERTY_NAME: "semrushkey"
    },
    queries : {
      domainOrganic : "http://api.semrush.com/?type=domain_organic&key=",
      urlOrganic : "http://api.semrush.com/?type=url_organic&key=",
      keywordDifficulty: "http://api.semrush.com/?type=phrase_kdi&key=",
      phraseOrganic : "http://api.semrush.com/?type=phrase_organic&key=",
      keywordVolume : "http://api.semrush.com/?type=phrase_this&key=",
      relatedQueries : "http://api.semrush.com/?type=phrase_related&key=",
      phraseQuestions: "https://api.semrush.com/?type=phrase_questions&key=",
      domainOverview : "http://api.semrush.com/?type=domain_rank&key=",
      countUnits : "http://www.semrush.com/users/countapiunits.html?key="
    },
    methods : {
      giveApiRest : function giveApiRest () {
        Utilities.sleep(200)
      },
      checkAccount : function checkAccount () {       
        
        try {
          if (!SemrushGlobal.data.API_KEY) return [false,"No valid API key found. Please run the Add Semrush Key function from the Spreadsheet menu under 'Helper functions'"]  
          var result = UrlFetchApp.fetch(SemrushGlobal.queries.countUnits + SemrushGlobal.data.API_KEY,{"muteHttpExceptions":true})
          
          if (typeof result.getContentText() === "string") {
            if(parseInt(result.getContentText()) > -1) { 
              return [true]
            }
            var parsedResult = JSON.parse(result.getContentText())
            if (parsedResult == 0) throw "The key:" + PropertiesService.getDocumentProperties().getProperty("semrushkey") + " has no credits left"
            if (parsedResult.data.error) throw parsedResult.data.error
              return [true]
              } 
          
            } catch (e) {
              return [false,e]
            } 
      }, 
      parseApiResponse  : function parseApiResponse(result,valueBoolean) { 
        var data = [], valueBoolean, filtered
        valueBoolean ? valueBoolean = 1 : valueBoolean = 0;
        var newLines = result.split("\n");

        try {
          for(i=valueBoolean;i<newLines.length;i++) {
            data.push(newLines[i].split(";"))
          }

          //this is inefficient to remove stubborn whitespace - nbed - need a better solution but it works for now
          for (var i = 0; i < data.length; i++) {
            for (var y = 0; y < data[i].length; y++) {
             data[i][y] = data[i][y].replace(/\n|\r\n/g,"")
            }
          }

          //also inefficient to get rid of null values in the result array
          var cleanArray = data.filter(function (line) {
            return line != "";
          });
          
          return cleanArray
        } catch(e) {
          return e
        }
      }  
      
    }
 
}


/* ---------------------------------------------------------------------------*
              SemrushGlobal Object END 
* -------------------------------------------------------------------------*/


/* ---------------------------------------------------------------------------*
              Helper functions START 
* -------------------------------------------------------------------------*/



var ROOT_ = {
  
    cacheDefaultTime: 1500,
    
    // Step 1 -- Construct a unique name for function call storage using the function name and arguments passed to the function
    // example: function getPaidApi(1,2,3) becomes "getPaidApi123"
    encode: function encode(functionName, argumentsPassed) {
      var data = [functionName].concat(argumentsPassed);
      var json = JSON.stringify(data);
      return Utilities.base64Encode(json);
    },
    
    //Step 2 -- when a user calls a function that uses a paid api, we want to cache the results for 25 minutes
    addToCache : function addToCache (encoded, returnedValues) { 
      
      var values = {
        returnValues : returnedValues
      }
      CacheService.getDocumentCache().put(encoded, JSON.stringify(values), this.cacheDefaultTime)
      //also set the properties service keys so we can loop through and delete the cache if necessary
      PropertiesService.getDocumentProperties().setProperty(encoded, JSON.stringify(values))
    },
    
    //Step 3 -- if the user repeats the exact same function call with the same arguments, we give them the cached result
    //this way, we don't consume API credits as easily. 
    checkCache : function checkCache(encoded) {
      
      var cached = CacheService.getDocumentCache().get(encoded);
      
      try {
        cached = JSON.parse(cached)
        return cached.returnValues
      } catch (e) {
        return false;
      }
    },
    onlyUnique : function(value, index, self) { 
      return self.indexOf(value) === index;
    },
    checkUrl : function (url) {
      if (!url || typeof url !== "string") return false
      if (typeof url === "string") {
        if (url.indexOf("http") === -1) return false
          }
      
      return true
    }

}

function CACHE_TEST (data,cache) {
  if (cache) {
    var cacheStringName = ROOT_.encode(arguments.callee.name,arguments);
    var cachedResult = ROOT_.checkCache(cacheStringName); 
    if (cachedResult) return cachedResult + " from Cache! :)";
  } 
  
  if (cache) ROOT_.addToCache(cacheStringName,data);
  return data
  
}

function addApiKey () {
  var ui = SpreadsheetApp.getUi();
  var key = SemrushGlobal.data.API_KEY;
  var response = "";
  
  if(key) {
    response = ui.prompt('SEMRUSH KEY FOR THIS DOCUMENT', 'Looks like you already have the following key: ' + key + '. You can set another key in the box below', ui.ButtonSet.OK_CANCEL);
    if (response.getSelectedButton() == ui.Button.OK) PropertiesService.getUserProperties().setProperty("semrushkey", response.getResponseText())
    return false
  } 
  
  response = ui.prompt('SEMRUSH KEY FOR THIS DOCUMENT', 'Set your key', ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() == ui.Button.OK) PropertiesService.getUserProperties().setProperty("semrushkey", response.getResponseText()) 

}


function CacheServiceOutOfGlobalScope (key) {
  return CacheService.getDocumentCache().remove(key)
}

function PropertyServiceOutOfGlobalScope (key) {
  return PropertiesService.getDocumentProperties().deleteAllProperties();
}

function flushAllCacheAndProperties () {
  var properties = PropertiesService.getDocumentProperties().getKeys()
  
  for (var i = 0 ; i < properties.length ; i ++ ) {
  if (properties[i] === SemrushGlobal.data.API_KEY_PROPERTY_NAME) continue;
    CacheServiceOutOfGlobalScope(properties[i])
  }
  
  PropertyServiceOutOfGlobalScope()
}


function checkSemrushAccount () {
  try {
  if (typeof SemrushGlobal.data.API_KEY !== "string" ||  !SemrushGlobal.data.API_KEY) {
    return Browser.msgBox("Oops, an Error has occurred","No valid API key found. Please run the Add Semrush Key function from the Spreadsheet menu under 'Helper functions'",Browser.Buttons.OK)  
  } 
    var result = UrlFetchApp.fetch(SemrushGlobal.queries.countUnits + SemrushGlobal.data.API_KEY,{"muteHttpExceptions":true}).getContentText()
    if (typeof result === "string" && result > -1) {
        return Browser.msgBox("You have : " + result + " API credits left")
      }
      var parsedResult = JSON.parse(result)
      if (parsedResult.data.error) throw parsedResult.data.error
    
  } catch (e) {
    return Browser.msgBox("Oops, an Error has occurred","Error: " + e, Browser.Buttons.OK)
  }
}

/* ---------------------------------------------------------------------------*
              Helper functions END 
* -------------------------------------------------------------------------*/



/* ---------------------------------------------------------------------------*
                MAIN functions START 
* -------------------------------------------------------------------------*/


/**
* Returns Semrush domain history for a specified domain from January 2012 onwards
*
* @param {"example.com"} domain REQUIRED The root domain, example: "nytimes.com", DO NOT include protocol (http/https)
* @param {"us"} db OPTIONAL The country database you want to search from. Default is US
* @param {201601} date OPTIONAL Leave this blank for current data. YYYYMM format for historical reports, note: always reports on the 15th of the month.
* @param {true} excludeHeaders OPTIONAL true to EXCLUDE column headers or false to include. Default is false.
* @param {true} cache OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)
* @return Returns organic keywords count, organic traffic, organic cost, adwords data
* @customfunction
*/

function DOMAIN_OVERVIEW_SEMRUSH(domain,db,date,excludeHeaders,cache) {
  try {
    if (!domain || domain.indexOf("http") > -1) return "Error: Enter a valid domain, do not include protocol";
    if (domain.map) throw "Sorry, the domain parameter cannot be an array of values. It needs to be a single string"
    var displayDate = "&display_date=", db = db || SemrushGlobal.data.DEFAULT_DB;
    typeof cache === 'boolean' && cache === false ? cache = false: cache = true
    
    var accountCheck = SemrushGlobal.methods.checkAccount() 
    if (!accountCheck[0]) return accountCheck[1] 
    
    date ? displayDate+= date + "15" : displayDate = "";
    SemrushGlobal.methods.giveApiRest();
    
    if (cache) {
      var cacheStringName = ROOT_.encode(arguments.callee.name,arguments);
      var cachedResult = ROOT_.checkCache(cacheStringName); 
      if (cachedResult) return cachedResult;
    } 
    
    var result = UrlFetchApp.fetch(SemrushGlobal.queries.domainOverview+SemrushGlobal.data.API_KEY+"&export_columns=Or,Ot,Oc,Ad,At,Ac&domain="+domain+"&database="+db+displayDate).getContentText()
    if (result.indexOf("ERROR") > -1) throw result.trim()
    if (cache) ROOT_.addToCache(cacheStringName, SemrushGlobal.methods.parseApiResponse(result,excludeHeaders));
    return SemrushGlobal.methods.parseApiResponse(result,excludeHeaders);
  } catch (e) {
    return e;
  }
  
  
}

/**
* Returns Semrush Organic keywords for a specified domain
*
* @param {"example.com"} domain REQUIRED The root domain, example: "nytimes.com", DO NOT include protocol (http/https)
* @param {true} filterBy OPTIONAL Use true to include the query in the filter or false to remove the query in the filter. Default is true
* @param {true} matchType OPTIONAL Use true for partial match, use false for exact match. Default is true, partial match 
* @param {"apartments"} query OPTIONAL The keyword you want to filter by. Relies on previous 2 parameters. Example: "brown shoes".
* @param {10} limit OPTIONAL Number from 1 to 10000
* @param {"us"} db OPTIONAL The country database you want to search from. Default is US
* @param {201601} date OPTIONAL Leave this blank for current data. YYYYMM format for historical reports, note: always reports on the 15th of the month.
* @param {true} excludeHeaders OPTIONAL true to EXCLUDE column headers or false to include. Default is false.
* @param {true} cache OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)
* @return Access organic keywords for a domain from semrush.com database. 
* @customfunction
*/


function DOMAIN_ORGANIC_KEYWORDS_SEMRUSH(domain,filterBy,matchType,query,limit,db,date,excludeHeaders,cache) {
  try {
    if (!domain || domain.indexOf("http") > -1) return "Error: Enter a valid domain, do not include protocol"
    if (domain.map) throw "Sorry, the domain parameter cannot be an array of values. It needs to be a single string"
    var displayDate = "&display_date=", filterOperator,filterBy,excludeHeaders,query = query || "", limit = limit || 1, db = db || SemrushGlobal.data.DEFAULT_DB, filterBy = filterBy && true, matchType = matchType && true
    typeof cache === 'boolean' && cache === false ? cache = false: cache = true
    
    var accountCheck = SemrushGlobal.methods.checkAccount() 
    if (!accountCheck[0]) return accountCheck[1]
    
    if (cache) {
      var cacheStringName = ROOT_.encode(arguments.callee.name,arguments);
      var cachedResult = ROOT_.checkCache(cacheStringName) ;
      if (cachedResult) return cachedResult;      
    }  
    
    date ? displayDate+= date + "15" : displayDate = "";
    filterBy ? filterBy = "%2B" : filterBy = "-";
    matchType ? matchType = "Co" : matchType = "Eq"
    if(query) query = "%7C" + query
    
    SemrushGlobal.methods.giveApiRest();
    
    var result = UrlFetchApp.fetch(SemrushGlobal.queries.domainOrganic+SemrushGlobal.data.API_KEY+"&display_limit="+limit+"&export_columns=Ph,Po,Pp,Pd,Nq,Cp,Ur,Tr,Tc,Co,Nr,Td&domain="+domain+"&display_sort=tr_desc&database="+db+"&display_filter="+filterBy+"%7CPh%7C"+matchType+query+displayDate).getContentText()
    if (result.indexOf("ERROR") > -1) throw result.trim()
    if (cache) ROOT_.addToCache(cacheStringName, SemrushGlobal.methods.parseApiResponse(result,excludeHeaders));
    return SemrushGlobal.methods.parseApiResponse(result,excludeHeaders);
  } catch (e) {
    return e;
  }
  
  
}


/**
* Returns Historical rankings for domain/keyword combination
*
* @param {"example.com"} domain REQUIRED The root domain, example: "nytimes.com", DO NOT include protocol (http/https)
* @param {"apartments"} query OPTIONAL The keyword you want to filter by. Relies on previous 2 parameters. Example: "brown shoes".
* @param {10} limit OPTIONAL Number from 1 to 10000
* @param {"us"} db OPTIONAL The country database you want to search from. Default is US
* @param {201601} date OPTIONAL Leave this blank for current data. YYYYMM format for historical reports, note: always reports on the 15th of the month.
* @param {true} excludeHeaders OPTIONAL true to EXCLUDE column headers or false to include. Default is false.
* @param {true} cache OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)
* @return Access organic keywords for a domain from semrush.com database
* @customfunction
*/

function HISTORICAL_RANKING_KEYWORD_SEMRUSH(domain,query,limit,db,date,excludeHeaders,cache) {
  
  try {
    if (!domain || domain.indexOf("http") > -1) return "Error: Enter a valid domain, do not include protocol"
    var displayDate = "&display_date=",filterBy = "%2B", matchType = "Eq", excludeHeaders,query = query || "", limit = limit || 1, db = db || SemrushGlobal.data.DEFAULT_DB
    typeof cache === 'boolean' && cache === false ? cache = false: cache = true
    var accountCheck = SemrushGlobal.methods.checkAccount() 
    if (!accountCheck[0]) return accountCheck[1] 
    
    if (cache) {
      var cacheStringName = ROOT_.encode(arguments.callee.name,arguments)
      var cachedResult = ROOT_.checkCache(cacheStringName) 
      if (cachedResult) return cachedResult
        } 
    date ? displayDate+= date + "15" : displayDate = "";
    
    SemrushGlobal.methods.giveApiRest();
    
    var result = UrlFetchApp.fetch(SemrushGlobal.queries.domainOrganic+SemrushGlobal.data.API_KEY+"&display_limit="+limit+"&export_columns=Po&domain="+domain+"&display_sort=tr_desc&database="+db+"&display_filter="+filterBy+"%7CPh%7C"+matchType+"%7C"+query+displayDate).getContentText()
    if (result.indexOf("ERROR") > -1) throw result.trim()
    if (cache) ROOT_.addToCache(cacheStringName, SemrushGlobal.methods.parseApiResponse(result,excludeHeaders));
    return SemrushGlobal.methods.parseApiResponse(result,excludeHeaders);
  } catch (e) {
    return e
  }
  
  
}




/**
* Returns Ranking Semrush Organic keywords per URL
* @param {"http://example.com"} url REQUIRED The exact URL you want data for, MUST include protocol (http/https)
* @param {"10"} limit OPTIONAL Number from 1 to 10,000, for number of results
* @param {"US"} db OPTIONAL The database, example "US" for American database. Default is US
* @param {true} excludeHeaders OPTIONAL true to EXCLUDE column headers or false to include. Default is false.
* @param {true} cache OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)
* @return Access organic keywords for a url from semrush.com database
* @customfunction
*/


function URL_ORGANIC_KEYWORDS_SEMRUSH(url,limit,db,excludeHeaders,cache) {
  try {
    var db = db || SemrushGlobal.data.DEFAULT_DB, limit = limit || 10;
    if (!url || url.indexOf("http") == -1) return "Error: Enter a valid URL, ensure you include the protocol";
    if (url.map) throw "Sorry, the URL parameter cannot be an array of values. It needs to be a single string"
    typeof cache === 'boolean' && cache === false ? cache = false: cache = true
    //semrush won't report on a homepage unless it has a trailing slash;
    if (url.match(/\//g).length < 3) url += "/";
    var accountCheck = SemrushGlobal.methods.checkAccount() 
    if (!accountCheck[0]) return accountCheck[1] 
   
    if (cache) {
      var cacheStringName = ROOT_.encode(arguments.callee.name,arguments);
      var cachedResult = ROOT_.checkCache(cacheStringName);
      if (cachedResult) return cachedResult;
    }
    SemrushGlobal.methods.giveApiRest();
    
    
    var result = UrlFetchApp.fetch(SemrushGlobal.queries.urlOrganic + SemrushGlobal.data.API_KEY+ "&display_limit="+limit+"&export_columns=Ph,Po,Nq,Cp,Co,Tr,Tc,Nr,Td&url="+url+"&database="+db).getContentText();
    if (result.indexOf("ERROR") > -1) throw result.trim()
    if (cache) ROOT_.addToCache(cacheStringName, SemrushGlobal.methods.parseApiResponse(result,excludeHeaders));
    return SemrushGlobal.methods.parseApiResponse(result,excludeHeaders);
  } catch(e) {
    return e;
  }
}


/**
* Returns Semrush Keyword Difficulty for a keyword 
*
* @param {"apartments"} query REQUIRED The keyword you want information for. Example: "brown shoes".
* @param {"us"} db OPTIONAL The country database you want to search from. Default is US
* @param {true} excludeHeaders OPTIONAL true to EXCLUDE column headers or false to include. Default is false.
* @param {true} cache OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)
* @return Access keyword difficulty for keyword from semrush
* @customfunction
*/

function KEYWORD_DIFFICULTY_SEMRUSH(query,db,excludeHeaders,cache) {
  try {
    if (!query) return "Error: Missing query";
    if (query.map) throw "Sorry, the query parameter cannot be an array of values. It needs to be a single string"
    typeof cache === 'boolean' && cache === false ? cache = false: cache = true
    var db = db || SemrushGlobal.data.DEFAULT_DB;
    
    var accountCheck = SemrushGlobal.methods.checkAccount() 
    if (!accountCheck[0]) return accountCheck[1] 
    
    if (cache) {
      var cacheStringName = ROOT_.encode(arguments.callee.name,arguments);
      var cachedResult = ROOT_.checkCache(cacheStringName); 
      if (cachedResult) return cachedResult;
    }
    SemrushGlobal.methods.giveApiRest();
    var result = UrlFetchApp.fetch(SemrushGlobal.queries.keywordDifficulty + SemrushGlobal.data.API_KEY+ "&export_columns=Ph,Kd&phrase="+query+"&database=" + db).getContentText();
    if (result.indexOf("ERROR") > -1) throw result.trim()
    if (cache) ROOT_.addToCache(cacheStringName, SemrushGlobal.methods.parseApiResponse(result,excludeHeaders));
    return SemrushGlobal.methods.parseApiResponse(result,excludeHeaders);
  } catch(e) {
    return e
  }
}

/**
* Returns Semrush organic search results for a specific keyword
*
* @param {"apartments"} query REQUIRED The keyword you want information for. Example: "brown shoes".
* @param {10} limit OPTIONAL Number from 10 to 20, for number of results. Default is 10
* @param {"us"} db OPTIONAL The country database you want to search from. Default is US
* @param {true} excludeHeaders OPTIONAL true to EXCLUDE column headers or false to include. Default is false.
* @param {true} cache OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)
* @return Access organic search results for a keyword from semrush.com database
* @customfunction
*/

function SERPS_SEMRUSH(query,limit,db,excludeHeaders,cache) {
 
  try {
    if (!query) return "Error: Missing query";
    if (query.map) throw "Sorry, the query parameter cannot be an array of values. It needs to be a single string"
    var db = db || SemrushGlobal.data.DEFAULT_DB, limit = limit || 10;
    typeof cache === 'boolean' && cache === false ? cache = false: cache = true
    var accountCheck = SemrushGlobal.methods.checkAccount() 
    if (!accountCheck[0]) return accountCheck[1]  
    
    
    if (cache) {
      var cacheStringName = ROOT_.encode(arguments.callee.name,arguments);
      var cachedResult = ROOT_.checkCache(cacheStringName);
      if (cachedResult) return cachedResult;
    }
    
    SemrushGlobal.methods.giveApiRest();
    var result = UrlFetchApp.fetch(SemrushGlobal.queries.phraseOrganic + SemrushGlobal.data.API_KEY+"&phrase="+query+"&export_columns=Dn,Ur,Fl&database="+db+"&display_limit="+limit).getContentText();
    if (result.indexOf("ERROR") > -1) throw result.trim()
    if (cache) ROOT_.addToCache(cacheStringName, SemrushGlobal.methods.parseApiResponse(result,excludeHeaders));
    return SemrushGlobal.methods.parseApiResponse(result);
  } catch (e) {
    return e
  }
}

/**
* Returns Related search queries for a keyword from Semrush
*
* @param {"apartments"} query REQUIRED The keyword you want information for. Example: "brown shoes".
* @param {10} limit OPTIONAL The number of results. Default is 1
* @param {"us"} db OPTIONAL The country database you want to search from. Default is US
* @param {true} excludeHeaders OPTIONAL true to EXCLUDE column headers or false to include. Default is false.
* @param {true} cache OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)
* @return Returns related queries for a specific keyword from semrush.com
* @customfunction
*/


function RELATED_QUERIES_SEMRUSH(query,limit,db,excludeHeaders,cache) {
  try {
    if (!query) return "Error: Missing query";
    if (query.map) throw "Sorry, the query parameter cannot be an array of values. It needs to be a single string"
    var limit = limit || 1, db = db || SemrushGlobal.data.DEFAULT_DB;
    typeof cache === 'boolean' && cache === false ? cache = false: cache = true
    var accountCheck = SemrushGlobal.methods.checkAccount() 
    if (!accountCheck[0]) return accountCheck[1]  
    
    if (cache) {
      var cacheStringName = ROOT_.encode(arguments.callee.name,arguments);
      var cachedResult = ROOT_.checkCache(cacheStringName); 
      if (cachedResult) return cachedResult;
    }
    SemrushGlobal.methods.giveApiRest();
    
    var result = UrlFetchApp.fetch(SemrushGlobal.queries.relatedQueries + SemrushGlobal.data.API_KEY+"&display_limit="+limit+"&export_columns=Ph,Nq,Cp,Co,Nr,Td&phrase="+query+"&database="+db+"&display_sort=nq_desc").getContentText();
    if (result.indexOf("ERROR") > -1) throw result.trim()
    if (cache) ROOT_.addToCache(cacheStringName, SemrushGlobal.methods.parseApiResponse(result,excludeHeaders));
    return SemrushGlobal.methods.parseApiResponse(result,excludeHeaders);
  } catch(e) {
    return e;
  }
  
}

/**
* Returns Keyword Volume from semrush
*
* @param {"apartments"} query REQUIRED The keyword you want information for. Example: "brown shoes".
* @param {"us"} db OPTIONAL The country database you want to search from, default is "us"
* @param {true} excludeHeaders OPTIONAL true to EXCLUDE column headers or false to include. Default is false.
* @param {true} cache OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)
* @return Returns search volume, cpc, etc..
* @customfunction
*/

function KEYWORD_VOLUME_SEMRUSH(query,db,excludeHeaders,cache) {
  try {
    if (!query) return "Error: Missing query";
    var db = db || SemrushGlobal.data.DEFAULT_DB;
    SemrushGlobal.methods.giveApiRest();
    typeof cache === 'boolean' && cache === false ? cache = false: cache = true
    var accountCheck = SemrushGlobal.methods.checkAccount() 
    if (!accountCheck[0]) return accountCheck[1] 
    
    if (query.map) throw "Sorry, the query parameter cannot be an array of values. It needs to be a single string"
    
    if (cache) {
      var cacheStringName = ROOT_.encode(arguments.callee.name,arguments);
      var cachedResult = ROOT_.checkCache(cacheStringName); 
      if (cachedResult) return cachedResult;
    }
    
    var result = UrlFetchApp.fetch(SemrushGlobal.queries.keywordVolume + SemrushGlobal.data.API_KEY+"&export_columns=Ph,Nq,Cp,Co&phrase="+query+"&database="+db).getContentText();
    if (result.indexOf("ERROR") > -1) throw result.trim()
    if (cache) ROOT_.addToCache(cacheStringName, SemrushGlobal.methods.parseApiResponse(result,excludeHeaders));
    return SemrushGlobal.methods.parseApiResponse(result,excludeHeaders);
    
  } catch (e) {
    return e;
  }
}


/**
* Returns Questions asked for a keyword from Semrush
*
* @param {"apartments"} query REQUIRED The keyword you want information for. Example: "brown shoes".
* @param {10} limit OPTIONAL The number of results. Default is 1
* @param {"us"} db OPTIONAL The country database you want to search from. Default is US
* @param {true} filterBySearchVolume OPTIONAL If you want to filter by search volume, use TRUE, default is FALSE
* @param {">"} searchVolumeFilter OPTIONAL Filters the search volume parameter (next). Use ">" for Greater than, or "<" for Less than. Default is ">" greater than
* @param {1000} searchVolume OPTIONAL A search volume number (per month) you want to filter results by. Both previous parameters need to be set for this to work.
* @param {true} excludeHeaders OPTIONAL true to EXCLUDE column headers or false to include. Default is false.
* @param {true} cache OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)
* @return Returns Questions asked for a specific keyword from semrush.com
* @customfunction
*/


function PHRASE_QUESTIONS_SEMRUSH(query,limit,db,filterBySearchVolume,searchVolumeFilter,searchVolume, excludeHeaders,cache) {
  try {
    if (!query) return "Error: Missing query";
    var limit = limit || 1, db = db || SemrushGlobal.data.DEFAULT_DB, searchVolumeFilter = searchVolumeFilter || ">";
    typeof cache === 'boolean' && cache === false ? cache = false: cache = true
    SemrushGlobal.methods.giveApiRest();
    var accountCheck = SemrushGlobal.methods.checkAccount() 
    if (!accountCheck[0]) return accountCheck[1]  
    
    if (cache) {
      var cacheStringName = ROOT_.encode(arguments.callee.name,arguments);
      var cachedResult = ROOT_.checkCache(cacheStringName); 
      if (cachedResult) return cachedResult;
    }
    searchVolumeFilter === ">" ? searchVolumeFilter = "Gt" : searchVolumeFilter = "Lt" 
    filterBySearchVolume? filterBySearchVolume = "&display_filter=%2B%7CNq%7C"+searchVolumeFilter+"%7C"+searchVolume: filterBySearchVolume = ""
    var result = UrlFetchApp.fetch(SemrushGlobal.queries.phraseQuestions + SemrushGlobal.data.API_KEY+"&phrase="+query+"&export_columns=Ph,Nq,Cp,Co,Nr,Td&database="+db+"&display_limit="+limit+"&display_sort=nq_desc"+filterBySearchVolume).getContentText();
    if (result.indexOf("ERROR") > -1) throw result.trim()
    if (cache) ROOT_.addToCache(cacheStringName, SemrushGlobal.methods.parseApiResponse(result,excludeHeaders));
    return SemrushGlobal.methods.parseApiResponse(result,excludeHeaders);
  } catch(e) {
    return e;
  }
  
}


/* ---------------------------------------------------------------------------*
                       MAIN functions END 
* -------------------------------------------------------------------------*/

« Transformer Google Sheet en une application »

Si vous êtes allé sur le lien ci-dessus, vous avez pu voir une démonstration de Google Apps Script avec Google Sheet : ils expliquent, à partir d’un Google Sheet qui contient des adresses, comment générer une carte google Maps (avec les adresses en question), et comment envoyer ca automatiquement par mail.

Apps Script vous permet de connecter votre Google Sheet à d’autres APIS, Google Sheet n’est plus un simple tableur mais un véritable outil multifonctions.

Pour apprendre à le faire pas à pas, voici le lien Google CodeLabs : https://codelabs.developers.google.com/codelabs/apps-script-intro/#0

Ok, ce n’est pas le cas le plus SEO… mais si vous avez de l’imagination, vous avez plusieurs briques à assembler pour faire plein de spam choses intéressantes.

Google Sheets, l’outil multifonction (qui ne remplace pas une vraie boîte à outils)

Voilà… c’est déjà pas mal pour le moment. J’espère que cela vous donnera envie de découvrir d’autres outils. Comme je l’avais dit en introduction du 1er article : Google Sheet est un outil multifonction. Un outil multifonction, c’est bien, c’est pratique, ca dépanne bien… mais à un moment, lorsqu’on veut aller plus loin, autant acheter une vraie boite à outils, ou des outils spécialisés. Le couteau suisse n’est pas toujours la meilleure solution.

Merci d’avoir lu jusqu’ici et n’hésitez pas à me proposer d’autres fonctions qui m’auraient échappées.


Laissez un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Commentaire sur “Google Sheets : les fonctions personnalisées (apps scripts)