时间戳

首页 > 解决方案 > Google Web App 未能将光标正确放置在 Google 表格中

google-apps-script - Google Web App 未能将光标正确放置在 Google 表格中

问题描述

我正在创建一个谷歌网络应用程序来打开一个谷歌表格，我已经在其中添加了一行，然后将光标放在脚本添加了新文本的单元格中。一切正常，但最后一步 - 光标放置。

代码如下：

代码.gs

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate();
}

function addSong(objArgs) {
    var ss = SpreadsheetApp.openById('id');
    var url = ss.getUrl();
    var sheet = ss.getActiveSheet();
    var lastRow = sheet.getLastRow()
    sheet.appendRow([lastRow+1]);
    SpreadsheetApp.flush();
    var range = sheet.getRange(sheet.getLastRow(), 1);
    var songTitle = objArgs.songTitle;
    Logger.log('songTitle: ' + songTitle)
    var namedRange = sheet.getRange("Title");
    var newRange = sheet.getRange(sheet.getLastRow(), namedRange.getColumn())
    newRange.setValue(songTitle);
    SpreadsheetApp.setActiveSpreadsheet(ss);
    SpreadsheetApp.setActiveSheet(sheet);
    SpreadsheetApp.setActiveRange(newRange);
    //I suspect the error is in the lines below   
  return {
    url: url,
    newRange: newRange
    };
}

索引.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
<br>Fill in field below with Song Title.<br>
Then click button to add new song to Catalog.<br><br>
    <input id="idSongTitle" type="text" placeholder="Song Title"><br><br>
    <button onclick="saveUserInput()">Add song to Catalog</button>    

    <script>
      window.saveUserInput = function() {
        var songTitle = document.getElementById('idSongTitle').value;
        console.log('songTitle: ' + songTitle)
        google.script.run       
          .withSuccessHandler(openCatalog)
          .addSong({songTitle:songTitle})
      }
       // This function is detritus left over from something else. Doesn't seem to be impacting the rest of this.
       function openCatalog(results){
           window.open(results.url, '_blank').focus();
       }
    </script>
  </body>
</html>

修改后的代码：

代码.gs

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate();
}
function addSong(objArgs) {
    var id= "1-yN6yAGUuOp84apeN2Cwaq25rPD63qb_m2Oe-MSMaIM"
    var ss = SpreadsheetApp.openById(id);
    var url = ss.getUrl();
    var sheet = ss.getActiveSheet();
    var lastRow = sheet.getLastRow()
    sheet.appendRow([lastRow+1]);
    SpreadsheetApp.flush();
    var range = sheet.getRange(sheet.getLastRow(), 1);
    var songTitle = objArgs.songTitle;
    Logger.log('songTitle: ' + songTitle)
    var namedRange = sheet.getRange("Title");
    var newRange = sheet.getRange(sheet.getLastRow(), namedRange.getColumn())
    newRange.setValue(songTitle);
    Logger.log(url)
return {
    url: url
    };
}

索引.html

    <!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
<br>Fill in field below with Song Title.<br>
Then click button to add new song to Catalog.<br><br>
    <input id="idSongTitle" type="text" placeholder="Song Title"><br><br>
    <button onclick="saveUserInput()">Add song to Catalog</button>    

    <script>
      window.saveUserInput = function() {
        var songTitle = document.getElementById('idSongTitle').value;
        console.log('songTitle: ' + songTitle)
        google.script.run       
          .withSuccessHandler(openCatalog)
          .addSong({songTitle:songTitle})
      }
       function openCatalog(results){
          window.open(results.url, '_blank').focus();
       }
    </script>
  </body>
</html>

最终脚本

代码.gs

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate();
}
function addSong(objArgs) {
    var id= "1-yN6yAGUuOp84apeN2Cwaq25rPD63qb_m2Oe-MSMaIM"
    var ss = SpreadsheetApp.openById(id);
    var url = ss.getUrl();
    var sheet = ss.getActiveSheet();
    var lastRow = sheet.getLastRow()
    sheet.appendRow([lastRow+1]);
    SpreadsheetApp.flush();
    var range = sheet.getRange(sheet.getLastRow(), 1);
    var songTitle = objArgs.songTitle;
    var namedRange = sheet.getRange("Title");
    var newRange = sheet.getRange(sheet.getLastRow(), namedRange.getColumn())
    var newLastRow = lastRow+1
    newRange.setValue(songTitle);
    url = url + "#gid=1286827341" + "&range=" + newLastRow + ":" + newLastRow;
    Logger.log('url: ' + url)

    return {
    url: url
    };
}

索引.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
<br>Fill in field below with Song Title.<br>
Then click button to add new song to Catalog.<br><br>
    <input id="idSongTitle" type="text" placeholder="Song Title"><br><br>
    <button onclick="saveUserInput()">Add song to Catalog</button>    

    <script>
      window.saveUserInput = function() {
        var songTitle = document.getElementById('idSongTitle').value;
        console.log('songTitle: ' + songTitle)
        google.script.run       
          .withSuccessHandler(openCatalog)
          .addSong({songTitle:songTitle})
      }
       function openCatalog(url){
       window.open(url);
       }
    </script>
  </body>
</html>

轻微编辑

我换了...

return {
        url: url
        };

为了...

return url;

因为前者产生了 400 (Bad Request) 错误。

标签： google-apps-scriptweb-applicationsgoogle-sheets

解决方案

问题：

  • 服务器端：newRange是一个内置的范围对象，而不是合法的返回值1。

  • 客户端：没有做任何事情来使newRange活动范围。

解决方案：

  • 不要发送newRange，内置范围对象作为返回值。
  • 制作一个具有活动范围的 url 作为 url 片段并发送它。

示例片段：

代码.gs

//Url : https:docs.google.com/spreadsheets/[ID]/edit
//Url fragment : #gid=[SHEET_ID]&range=[RANGE_A1_NOTATION]
url = url + "#gid=" + sheet.getSheetId() + "&range=" + newRange.getA1Notation();
return url; //typeof url === "string"; No illegal return values present.

索引.html：

function openCatalog(url){
  window.open(url);
}

推荐阅读