4
\$\begingroup\$

I have this set of functions in my Google Sheet to create a Bitly bitlink whenever data in a row is changed. While this does work, it seems really slow to me. Looking at the Execution transcript, it looks like the lag is happening when the script queries the Sheet:

  1. Spreadsheet.getRange([L6]) [9.535 seconds]
  2. Range.getValue() [10.186 seconds]
  3. Spreadsheet.getRange([O6]) [20.228 seconds]
  4. Spreadsheet.getRange([O6]) [20.222 seconds]

The total script execution takes 40 to 80 seconds. This seems longer than it should take. Is there a better way to do this that is faster? Should I somehow be combining/batching the Sheet queries?

Here is the code:

/*
 * SETTINGS
 */

var bitlyToken = "abcdefghijklmnopqrstuvwxyz";
var logging = true;

/*
 * FUNCTIONS
 */

function _(logMsg) {
  if(logging) {
    Logger.log(logMsg);
  }
}

function getBitlink(longUrl) {
  var bitlyRequest = "https://api-ssl.bitly.com/v3/shorten?format=txt&access_token=" + bitlyToken + "&longUrl=" + longUrl;
  var options = {
    'method': 'get'
  };
  try {
    response = UrlFetchApp.fetch(bitlyRequest,options);
    response = response.getContentText();
    response = response.trim();
    return response;
  } catch(e) {
    return false;
  }
}

function getBitlinkClicks(bitlink) {
  var bitlyRequest = "https://api-ssl.bitly.com/v3/link/clicks?format=txt&unit=day&units=-1&rollup=true&access_token=" + bitlyToken + "&link=" + bitlink;
  var options = {
    'method': 'get'
  };
  try {
    response = UrlFetchApp.fetch(bitlyRequest,options);
    response = response.getContentText();
    response = response.trim();
    return response;
  } catch(e) {
    return false;
  }
}

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var editedCell = e.range;
  var rowEdited = editedCell.getRow();
  var longUrl = encodeURIComponent(ss.getRange("L"+rowEdited).getValue());
  var bitlink = '';

  _("longUrl: " + longUrl);
  if(longUrl.length > 0) {
    bitlink = getBitlink(longUrl);
    if(bitlink) {
      _("bitlink: " + bitlink);
      ss.getRange("N"+rowEdited).setValue(bitlink);
      bitlinkClicks = getBitlinkClicks(bitlink);
      if(bitlinkClicks) {
        _("bitlinkClicks: " + bitlinkClicks);
        ss.getRange("O"+rowEdited).setValue(parseInt(bitlinkClicks));
        ss.getRange("O"+rowEdited).setNote( 'Bitlink clicks as of ' + new Date() + '.' );
      }
    }
  }
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.