CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Arun Kumar Nagarajan
Arun Kumar Nagarajan

Reputation: 2417

Create new spreadsheet and show Google script

I've been trying to create a new spreadsheet through google script and google spreadsheet and show it to the user. I could create the sheet with SpreadsheetApp.create; however, I couldn't show it on the browser. When I try to search for it in my drive, it is showing up.

I noticed a method show for Sheet, so, I tried that with a single sheet that I created in the newly created Spreadsheet, however, that is not working.

Upvotes: 1

Views: 2445

Answers (2)

Cooper
Cooper

Reputation: 64032

Create and Show Spreadsheet From a StandAlone WebApp

You could do something like this with a webapp.

Code.gs:

function createSpreadsheet(name){
  var file=SpreadsheetApp.create(name);
  var sh=file.getActiveSheet();
  var fObj={};
  sh.appendRow(['FileName:',file.getName()]);
  sh.appendRow(['Url:',file.getUrl()]);
  sh.appendRow(['Sheet Name:',file.getSheetName()]);
  sh.appendRow(['Id:',file.getId()]);
  var hl=Utilities.formatString('<a href="%s" target="_blank" >%s</a>', file.getUrl(), file.getName());
  fObj['id']=file.getId();
  fObj['hl']=hl;
  return fObj;
}

function doGet(){
  return HtmlService.createTemplateFromFile('creatandshow').evaluate().setTitle('Create and Show Spreadsheet')
}

function include(filename){
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function getFileFolder(Id){
  var ss=SpreadsheetApp.openById(Id);
  var sh=ss.getActiveSheet();
  var file=DriveApp.getFileById(Id);
  var folders=file.getParents();
  while(folders.hasNext()){
    sh.appendRow(['Parent Folder:',folders.next()]);
  }
  SpreadsheetApp.flush();
}

script.html:

<script>

<script>
function createSpreadsheet(){
  var name=$('#ssName').val();
  if(!name) {
    alert('You did not enter a Spreadsheet Name');
  }else{
    google.script.run
    .withSuccessHandler(function(fObj){
      $('#sslinks').html(fObj.hl);
      google.script.run.getFileFolder(fObj.id);
    })
    .createSpreadsheet(name);
  }
}
</script>

resources.html:

<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no"/>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>

creatandshow.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('resources') ?>
    <?!= include('css') ?>
    <?!= include('script') ?>
  </head>
  <body>
    <div id="sslinks"></div>
    <div id="controls">
      <input  id="ssName" type="text" placeholder="Enter New Spreadsheet Name" size="60" />
      <input id="btn1" type="button" value="Create Spreadsheet" onClick="createSpreadsheet();" />
    </div>
  </body>
</html>

Here's what it looks like:

enter image description here

Upvotes: 1

Gerneio
Gerneio

Reputation: 1340

Using GAS, you can't navigate the user to another spreadsheet programmatically.

What you could do is using this show method and present HTML content with a link to the newly created spreadsheet. They'd obviously have to click the link to proceed.

There might be some other things that we can do, but we'd need to know more context as far as what you're trying to accomplish and why.

Upvotes: 1

Related Questions