I got this code from here : Display Spreadsheet Data to HTML Table
thanks to the great work of Cooper.
function htmlSpreadsheet(ssO) {
var br='<br />';
var s='';
var hdrRows=1;
var ss=SpreadsheetApp.openById(ssO.id);
var sht=ss.getSheetByName(ssO.name);
var rng=sht.getDataRange();
var rngA=rng.getValues();
s ='<table>';
for(var i=0;i<rngA.length;i )
{
s ='<tr>';
for(var j=0;j<rngA[i].length;j )
{
if(i<hdrRows)
{
s ='<th id="cell' i j '">' '<input id="txt' i j '" type="text" value="' rngA[i][j] '" size="20" onChange="updateSS(' i ',' j ');" />' '</th>';
}
else
{
s ='<td id="cell' i j '">' '<input id="txt' i j '" type="text" value="' rngA[i][j] '" size="20" onChange="updateSS(' i ',' j ');" />' '</th>';
}
}
s ='</tr>';
}
s ='</table>';
s ='</body></html>';
var namehl=Utilities.formatString('<h1>%s</h1>', ss.getName());
var shnamehl=Utilities.formatString('<h2>%s</h2>', sht.getName());
var opO={hl:s,name:namehl,shname:shnamehl};
return opO;
}
function updateSpreadsheet(updObj) {
var i=updObj.rowIndex;
var j=updObj.colIndex;
var value=updObj.value;
var ss=SpreadsheetApp.openById(updObj.id);
var sht=ss.getSheetByName(updObj.name);
var rng=sht.getDataRange();
var rngA=rng.getValues();
rngA[i][j]=value;
rng.setValues(rngA);
var data = {'message':'Cell[' Number(i 1) '][' Number(j 1) '] Has been updated', 'ridx': i, 'cidx': j};
return data;
}
function doGet() {
var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
return userInterface.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function getAllSpreadSheets() {
var files=DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
var s = '';
var vA=[['Select Spreadsheet',0]];
while(files.hasNext())
{
var file = files.next();
var fileName=file.getName();
var fileId=file.getId();
vA.push([fileName,fileId]);
}
//return vA;
return {array:vA,type:'sel1'};
}
//working on this function right now 2017/11/08
function getAllSheets(ssO) {
var ss=SpreadsheetApp.openById(ssO.id);
var allSheets=ss.getSheets();
var vA=[['Select Sheet']];
for(var i=0;i<allSheets.length;i )
{
var name=allSheets[i].getName();
vA.push([name]);
}
return {array:vA,type:'sel2'};
}
What I am trying to do is on a Single sheet. That is I don't want to browse all sheets and select among them~
I have tried modifying this code
function getAllSpreadSheets() {
var files=DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
var s = '';
// var vA=[['Select Spreadsheet',0]];
while(files.hasNext())
{
// var file = files.next();
var fileName=file.getName();
var fileId=file.getId();
vA.push([fileName,fileId]);
}
//return vA;
return {array:vA,type:'sel1'};
}
I have used sheet Id instead of file.getId(), But It just don't work. Please help me.
CodePudding user response:
Change:
var files = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS)
To getFileById()
:
var file = DriveApp.getFileById("sheet Id");
Then remove the loop:
function getSingleSpreadSheet() {
var file = DriveApp.getFileById("sheet Id")
var fileName = file.getName()
var fileId = file.getId()
var vA = []
vA.push([fileName, fileId])
return {
array: vA,
type:'sel1'
}
}