Create a Google Sheet then publishing it via Google Apps Script

0
votes

Using Google Apps Script, I know how to create a new sheet:

var newSheet = SpreadsheetApp.create("newSheet");

And I know how to publish this sheet by hand from the created sheet:

File ==> Publish to the web.

But how can I publish and get the URL for the published sheet I created without the point/click method above? I would like a piece of code that resembles:

var newSheet = SpreadsheetApp.create("newSheet");
var pubSheet = newSheet.publish(); 
var pubURL   = pubSheet.getURL();
2
google-apps-scriptgoogle-sheetsweb-publishing
Welcome to StackOverflow. Please read and follow the posting guidelines in the help documentation, as suggested when you created this account. On topic and how to ask apply here. StackOverflow is not a design, coding, research, or tutorial service. stackoverflow.com/help/how-to-ask - NotZack
"I want code that does this" Ok? And? What is your specific question that you are having trouble with? For reference: developers.google.com/apps-script/reference/spreadsheet - tehhowch
Is it possible to publish a Google Spreadsheet using Google Apps Script without doing it by hand? - Preston Faulk
OP failed to actually do research: duplicate of (take your pick): stackoverflow.com/questions/38533957/… stackoverflow.com/questions/13016395/… stackoverflow.com/questions/40476324/… - tehhowch

2 Answers

1
votes

Edit:

As comment by tehhowch, your answer was already on SO. This answer is from STTP Smart Triathlon Training on SO question how to publish to the web a spreadsheet using drive API and GAS

You must first active Drive API from Console API (See Enable the Drive Platform) and activate it in your script (Resources > Google Advanced Services > Click on "Activate" on "Drive API" row )

//this function publish to the web the document given by ID (google sheets or docs)
function publishToWeb(){ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var fileId = ss.getId();
  var revisions = Drive.Revisions.list(fileId); 
  var items = revisions.items; 
  var revisionId =items[items.length-1].id; 
  var resource = Drive.Revisions.get(fileId, revisionId); 
  resource.published = true;
  resource.publishAuto = true;
  resource.publishedOutsideDomain = true;
  Drive.Revisions.update(resource, fileId, revisionId); 
}

First answer:

As seen on the documentation page, you can use the File.setSharing(accessType, permissionType) method like :

// Get Spreadsheet and Id
var ss = SpreadsheetApp.getActiveSpreadsheet();
var id = ss.getId();

// Get the file object with the Spreadsheet Id
var file = File.getFileById(id);
// Set sharing parameters so ANYONE can VIEW this spreadsheet
file.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
// Get the Spreadsheet url, who is now accessible by anyone
var url = ss.getUrl();

You can't just share one sheet by this method, but you can make something.

See also File.getFileById(id), SpreadsheetApp.getUrl(), SpreadsheetApp.getId()

0
votes

Building off of Pierre's input to answer Preston's question about how to get the url of the newly published webpage:

The url will follow this format 'https://docs.google.com/spreadsheets/d/'+fileId+'/pubhtml. Thanks to @tanaike for this insight.

Here is how I took the code provided by Pierre and used it to publish the active sheet and then paste the published url in a free cell in another sheet.

function getFirstEmptyRow() {
  var spr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Zapier");
  var column = spr.getRange('A:A');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct][0] != "" ) {
    ct++;
  }
  //Browser.msgBox(ct);
  return (ct+1);
}

function publishToWeb(){ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var fileId = ss.getId();
  var revisions = Drive.Revisions.list(fileId); 
  var items = revisions.items; 
  var revisionId =items[items.length-1].id; 
  var resource = Drive.Revisions.get(fileId, revisionId); 
  resource.published = true;
  resource.publishAuto = true;
  resource.publishedOutsideDomain = true;
  Drive.Revisions.update(resource, fileId, revisionId);
  
  
  
  var row = getFirstEmptyRow();
  var zapier = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Zapier");
  
  var url = zapier.getRange(row, 2);

  url.setValue('https://docs.google.com/spreadsheets/d/'+fileId+'/pubhtml');
  
}