CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Garth
Garth

Reputation: 53

SpreadsheetApp.getActive()

enter image description here

I have started on a journey of building an app (I'm not an IT professional) and I received some support with connecting my app with Google sheets.

Whilst all this works, I am seeking some guidance on an edit if this is possible. Currently the app will pull data from Google Sheets to the device. As you can see below in the code this is based on data in sheets rather than in ranges. Is it possible to change this so that I could store all the look up data in one sheet and in ranges, I have more to add and I may well end up with many sheets containing minimal data within.

if ( e.parameter.func == "static" ) {
  var ss = SpreadsheetApp.getActive();
  var shFlr = ss.getSheetByName("Floors").getDataRange().getValues();
  var shRm = ss.getSheetByName("Rooms").getDataRange().getValues();
  var shItm = ss.getSheetByName("Items").getDataRange().getValues();
  var shCnd = ss.getSheetByName("Condition").getDataRange().getValues();
  var shBldg = ss.getSheetByName("Buildings").getDataRange().getValues();
  var shUOM = ss.getSheetByName("UOM").getDataRange().getValues();//Projects Added
  return ContentService.createTextOutput(JSON.stringify({"UOM":shUOM,"Buildings":shBldg,"Floors":shFlr,"Rooms":shRm,"Items":shItm,"Condition":shCnd,})).setMimeType(ContentService.MimeType.JSON);

If anyone can describe how I could edit this I would be most grateful.

Upvotes: 0

Views: 1211

Answers (2)

Garth
Garth

Reputation: 53

function doGet(e) {

  if ( e.parameter.func == "static" ) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("ProjectSetUp");
    var rgProj = sh.getRange("A3:A3").getValues();

Upvotes: 1

Cooper
Cooper

Reputation: 64062

Try this:

function myfunction() {
  const ss=SpreadsheetApp.getActive();
  const shts=["Floors","Rooms","Items","Condtion","Buildings","UOM"];//These are the names of the individual sheets and there order determine the header location for each individual sheet.
  var sh=ss.getSheetByName('Combined');//I named the sheet Combined you can name it whatever you wish
  if(!sh) {var sh=ss.insertSheet('Combined');}//This creates the sheet if it's not already there
  sh.clear();//this clears the combined sheet
  sh.appendRow(shts);//This puts the header titles on the combined sheet
  shts.forEach(function(name,i){
    var s=ss.getSheetByName(name);//This gets the individual sheets
    var v=s.getRange(1,1,s.getLastRow(),1).getValues();//this gets each sheets one column of data
    sh.getRange(2,i+1,v.length,1).setValues(v);//this puts the data into each column of the combined sheet under that sheets name
  });
}

I haven't debugged this at all. So you will probably have to debug it. I know your hoping that it works...so am I.

Upvotes: 0

Related Questions