Home > Mobile >  Select one Particular sheet instead of listing all the sheets. Google app script, Code
Select one Particular sheet instead of listing all the sheets. Google app script, Code

Time:02-11

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'
  }
}
  • Related