Modify Tinyurl Google Script Function for Sheets

Question

I've been using this function to shorten links (Get range, if length over 200, copy and paste over new tinyurl link in same cell). But I wanna modify it to take active cell or active range instead of input range from UI prompt response. In this case I probably wouldn't need the if(x.length > 200) condition either. I've tried to research for some solutions that I could implement but its too complex for my beginner skills and understanding of original code to modify. Is there an easy fix I could do to it?

function tinyUrl() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();
  var final = [];
  var response = ui.prompt('Enter range:');
  if(response.getSelectedButton() == ui.Button.Ok) {
    try{
      var values = [].concat.apply([], ss.getRange(response.getResponseText()).getValues()).filter(String);
      SpreadsheetApp.getActiveSpreadsheet().toast('Processing range: '+ss.getRange(response.getResponseText()).getA1Notation(),'Task Started');
      values.forEach(x => {
        if(x.length > 200) {
          final.push(["IMPORTDATA(concatenate(\"http:// tiny url.com/api-create.php?url="+x+"\"),\"\")"]);
        }else{
          final.push(["=HYPERLINK(\""+x+"\")"]);
        }
      })
    }catch(e){
      SpreadsheetApp.getUi().alert(response.getResponseText()+' is not valid range!');
    }
  }else{
    return;
  }
  var r = response.getResponseText().split(":");
  if(r[0].length=1 &&r[1].length == 1){
    ss.getRange(r[0]+"1:"+r[0]+final.lenght).setFormulas(final);
  }else{
    ss.getRange(r[0]+":"+r[0].slice(0,1)+final.length).setFormulas(final);
  }
}

Argyll · Answer 1 · 2022-07-07T22:07:28.807

The exact solution depends on your actual application. I think your question is how to detect where to apply your custom function. But let's take a step back.

There are two ways to interact with existing data in sheet and a custom function via AppsScript.

One is that you can directly call your custom function with ranges in the sheet. If the input range is a single cell, the data is read directly. If the input range spans more than a single cell, the data is read as nested lists: a list of columns which are lists of rows. For example, A1:B2 will be read as [[A1, B1], [A2, B2]].

So, for example, you can always call your custom function tinyurl() wherever you input url in your sheet and let your custom function decide when to shorten it. Since your custom function is called in-place, there is no detection issue; UI prompt is not required.

The downside is that if you call your custom function in too many places, there will be delay in getting results. And I don't think the results are always stored with the sheet. (ie. when you open the sheet again, all cells with tinyurl() may refresh and cause delay.)

Second method is via the Range Class which is what you are using. Instead of using UI prompt though, you can add onEdit() trigger to your custom function and let your function either check for currently selected cell via SpreadsheetApp.getActive().getActiveRange() or scan for urls over the sheet where you intend for user inputs to be stored.

The downside of using onEdit() trigger is the UI lag. Relying on actively selected range can also be problematic. Yet if you scan over the whole sheet, well, you have to do that. At the end though, you get to store the resultant URLs permanently with the sheet. So after the initial lag, you won't have delays in the future.

In this route, you may find getLastRow(), getLastColumn() in Sheet and getNextDataCell() in Range convenient. If you choose to process the whole sheet, you may instead use the onOpen() trigger.

I would prefer to store all URLs in one place and use the 1st option. Thus, the custom function is only called once and that's useful for minimizing delay. Other parts of the sheet can reference cells in that centralized range.

The exact solution depends on your actual application.

Modify Tinyurl Google Script Function for Sheets

1 Answers1