0

I am creating a Google Web App to open a Google Sheet that I have appended a row to and then place the cursor in the cell into which the script has added the new text. Everything works but the last step--the cursor placement.

Code follows:

Code.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
    };
}

Index.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>

Revised Code:

Code.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
    };
}

Index.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>

Final Script

Code.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
    };
}

Index.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>

Slight Edit

I switched out...

return {
        url: url
        };

for...

return url;

because the former was producing 400 (Bad Request) errors.

Bee Tee
  • 129
  • 2
  • 15
  • 2
    I think that the error occurs on the client-side code. Have you already took a look to the web browser console? – Rubén Feb 22 '19 at 22:32
  • Possible duplicate of [Google app script: Send mail, inlineImage from js File](https://stackoverflow.com/questions/52588200/google-app-script-send-mail-inlineimage-from-js-file) – TheMaster Feb 23 '19 at 00:10
  • Why are your using `HtmlService.createTemplateFromFile('Index').evaluate();`? I don't see any scriptlets in it. – Cooper Feb 23 '19 at 02:32
  • Hey @Rubén, hate to ask dumb questions, but I'm new at this. What/where is the "web browser console"? – Bee Tee Feb 23 '19 at 08:37
  • 1
    @BeeTee, if you use Ctrl+Shift+J from Chrome, a window appears in the right hand side of the screen, make sure the Console tab is selected. This is where the output of client/browser side console.log() calls appears. The browser console is very powerful tool, even a client side debugger for JS. So it might be worth spending some time figuring out how it works. – P Burke Feb 23 '19 at 09:16
  • @Cooper, that was code carried over from a similar thing I was trying to do. Probably is wrong (as you note) because that other script was creating a new Google doc whereas this one is simply adding content to an already existing Google sheet. But, I am at a loss to know what to replace it (HtmlService.createTemplateFromFile('Index').evaluate();) with. – Bee Tee Feb 23 '19 at 10:52
  • I wasn't sure if it was wrong or not. I was under the impression that the evaluate just used to evaluate scriplets. – Cooper Feb 23 '19 at 11:19
  • Hey @TheMaster, I have looked at the "Possible duplicate of Google app script: Send mail, inlineImage from js File" and have no idea what its import is with regard to the reason my script won't open up the sheet I have modified with this script. – Bee Tee Feb 23 '19 at 12:31
  • Read my answer and the references there. `newRange` is not a legal value. – TheMaster Feb 23 '19 at 13:18
  • @TheMaster. Will do. Question: Can newRange be made a legal value? – Bee Tee Feb 23 '19 at 14:08
  • Can you confirm that it works as expected, if you remove `newRange` from the `return`? – TheMaster Feb 23 '19 at 15:02
  • @TheMaster, it does not work as expected. It does everything I expect it to do (add the new row to the correct spreadsheet>sheet), but it does not then open that sheet and position cursor in the cell with the new song title in it. – Bee Tee Feb 23 '19 at 16:05
  • Can you edit your question with the latest not-working script, after you've removed newRange? – TheMaster Feb 23 '19 at 16:36
  • @TheMaster. Posted revised code. – Bee Tee Feb 23 '19 at 16:44
  • Why have you commented out `openCatalog()` function and the successHandler? – TheMaster Feb 23 '19 at 16:59
  • @TheMaster: I should not have. Fixed (thanks for noting that). So, the code is doing everything I want it to do including (now, with that fix), opening the modified spreadsheet, except the active range (added row) bit is not displaying, nor is the newRange the active range. Rather, the spreadsheet simply opens and the active range is whatever it was before I ran the script. – Bee Tee Feb 23 '19 at 20:13

1 Answers1

0

Issue:

  • Server-side: newRange is a inbuilt range object and not a legal return value1.

  • Client-side: Nothing has been done to make the newRange the active range.

Solution:

  • Do not send newRange, inbuilt range object as a return value.
  • Craft a url with active range as url fragment and send it instead.

Sample snippets:

Code.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.

Index.html:

function openCatalog(url){
  window.open(url);
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • This is working: `url = url + "#gid=1286827341" + "&range=" + newRange.getA1Notation(); Logger.log('url: ' + url) //return url; //typeof url === "string"; No illegal return values present. return { url: url };` – Bee Tee Feb 23 '19 at 22:59
  • And thank you for sharing your time and knowledge @TheMaster. I now see what was going on. I had to build a url to the spreadsheet, the sheet, and the cell in language that was valid for the return command. – Bee Tee Feb 23 '19 at 23:01
  • You don't need to return a object `{}`. That's my point. Just send the string and and open it. See how I wrote the index.html.(There's no `results` or `results.url`) Also, hard coding the number (sheet ID) is never a good idea. – TheMaster Feb 23 '19 at 23:02
  • As to hard coding, I know. I'll deal with that later. I posted the working "final code" which I hacked a little so that at the end of the whole thing, the entire last (new) row is selected. What I don't know how to do though is add some code which makes that part of the spreadsheet the visible part of the spreadsheet (sheet has around 500 lines/songs). The default view I am getting is the top portion of the sheet. Any ideas? – Bee Tee Feb 23 '19 at 23:35
  • 1
    @BeeTee You mean like a filter? You might wanna ask a new question providing necessary details. – TheMaster Feb 24 '19 at 08:53