javascript - Google Spreadsheet——使用 ImportXML() 将 Google 搜索结果导入单元格后，将单元格值、Google 重定向 url 转换为实际 url

Question

示例单元格值为“https://www.google.com/url?q=http://en.wikipedia.org/wiki/Boricua_College&sa=U&ei=mTTIU4fhEs_jsASNy4G4Dg&ved=0CBQQFjAAOAE&usg=AFQjCNEnOjYOji3mdwT3_FS0L1_NCFk7GQ ”(尽管在电子表格中所有 URL 均为纯文本)，理想情况下我想将其替换为“http://en.wikipedia.org/wiki/Boricua_College ”。当我在脚本编辑器中运行程序时，电子表格会按照预期提示我输入列字母，但之后什么也不做。它不会删除 #N/A 值，也不会 trim 任何 URL。

function trimLink() {
  var s = SpreadsheetApp.getActiveSheet();
  // set column to trim
  var c = Browser.inputBox("Please", "Type in the column name (e.g.: A, B, etc.)",   Browser.Buttons.OK_CANCEL);
  if (c != "cancel") {
   c = c.charCodeAt(0) - 64;
  }
  var r, v;
  // set length to cut from beginning of string
  var begLink = "https://www.google.com/url?q=";
  var begLen = begLink.length;
  var endLen;
  try
  {
    if(c != "cancel")
    {
      r = 2; // first row is row two
      while (r <= s.getLastRow()) {
        v = s.getRange(r, c).getValue();
        // #N/A is the result of failed importXML
        if (v != "" && v != "#N/A") {
          // initialize variable to hold value of string
          var link = String(v);
          for (var k = 0; k < link.length; k++) {
            // link must be trimmed at char & (&sa)
            if (link.charAt(k) == '&') {
              // set length to cut from end of string
              endLen = k;
              break;
            }
          }
          v = link.substr(begLen, endLen - 1);
        }
        else {
         v = "";
        }
        r++;
      }
    }
  } catch (e) {Browser.msgBox("Error Alert:", e.message, Browser.Buttons.OK);}
}

Answer 1

主要问题是我没有为单元格分配新值。最重要的是，我使用了 substr 而不是 substring，但直到我的程序成功将单元格值更改为其他值之后才意识到。

function trimLink() {
  var s = SpreadsheetApp.getActiveSheet();
  // input relevant column
  var c = Browser.inputBox("Please", "Type in the column name (e.g.: A, B, etc.)",   Browser.Buttons.OK_CANCEL);
  // represent letter input as column number
  if (c != "cancel") {
   c = c.charCodeAt(0) - 64;
  }
  var r, link;
  // length to cut from beginning of string
  var begLink = "https://www.google.com/url?q=";
  var begLen = begLink.length;
  var endLen = 0;
  try
  {
    if(c != "cancel")
    {
      r = 2; // first row is row two
      while (r <= s.getLastRow()) {
        link = String(s.getRange(r, c).getValue());
        // #N/A is the result of failed importXML
        if (link != "" && link != "#N/A") {
          while (link.charAt(endLen) != '&') {
            endLen++
          }
          s.getRange(r,c).setValue(link.substring(begLen, endLen));
        }
        else {
          // clear cell if importxml (outside of this program) had failed to extract a g search url
          s.getRange(r, c).setValue("");
        }
        r++;
      }
    }
  } catch (e) {Browser.msgBox("Error Alert:", e.message, Browser.Buttons.OK);}
}