How to get a range from a sheet and exclude some columns

[Wilson Galter]

Hello,

is it possible to get the results of the following range (3, 1,lastRow-2,7)" without column 3 and/or 6? 

           var sheet = SpreadsheetApp.openById('11CrzziypWFfXREsdfsdfsdfGaUF_MfAn6dJhBOY').getSheetByName('employeVales');

           var lastRow = sheet.getLastRow();

           var cell = sheet.getRange(3, 1,lastRow-2,7).getValues();

[Jacopo Rumi]

No. Get the whole of it and then rid yourself of the unwanted columns.

Create this prototype:

Array.prototype.transpose = function() { var a = this; return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); }); }

then, assuming you put the values in the array data_area

var data_area = sheet.getRange(3, 1,lastRow-2,7).getValues();

transpose the array, because getValues() returns an array of rows

data_area = data_area.transpose;

now you have an array of columns

'splice' away the unwanted colums:

data_area.splice(2,1); // removes the second column... count starts at 0

data_area.splice(4,1); // removes the second column... count starts at 0 and we have already removed a column - of course, index would be 5 if you performed this operation first

if now you need an array or rows again, just transpose again

data_area = data_area.transpose;

Have fun!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ec6f3297-e95f-455b-9177-9f4086341f07%40googlegroups.com.

[Wilson Galter]

Hi Jacopo,

I tried your suggestion but I get an error TypeError: Cannot find function splice in object function () {...}. 

I've tried some variations as well. nothing works.

how does this prototype know to take the array of variable cell and create something with it?

           var sheet = SpreadsheetApp.openById('11CrzzY').getSheetByName('employeeVales');

           var lastRow = sheet.getLastRow();

           var cell = sheet.getRange(3, 1,lastRow-2,7).getValues();

   

Array.prototype.transpose = function() { var a = this; return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); }); }

  cell = cell.transpose;

 Logger.log(cell)

cell.splice(2,1); 

cell.splice(4,1); 

cell = cell.transpose;

in my Logger.log() I get the entire function:

Logs:

[19-11-29 12:05:33:315 PST] 

function () {

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[Jacopo Rumi]

Sorry, Wilson!

I was in a hurry and forgot the round brackets :)

this means assigns a function to the variable cell:

cell = cell.transpose;

This is why you get this error: Cannot find function splice in object function ()

You should write instead:

cell = cell.transpose();

Try now :)

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ec6f3297-e95f-455b-9177-9f4086341f07%40googlegroups.com.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/61d3ffde-ae8d-4b0a-ad56-56335ecdb28d%40googlegroups.com.

[Wilson Galter]

This really cool. It worked perfectly!

I don't understand how this function knows to do something to the array cell when it has nothing reffering to it?

Thank you for the attention!

Now with your function it does this

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ec6f3297-e95f-455b-9177-9f4086341f07%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[Jacopo Rumi]

Hi, Wilson.

You're welcome!

Il giorno ven 29 nov 2019 alle ore 23:43 Wilson Galter <wilson...@gmail.com> ha scritto:

This really cool. It worked perfectly!

I don't understand how this function knows to do something to the array cell when it has nothing reffering to it?

---> to get the hang of it,  this is where the 'magic' happens :)   var a = this;

the rest is quite compact and indeed a bit cryptic :)

you would rewrite the prototype as a function:

function transpose(a) { return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); }); }

and in your code:

cell = transpose(cell);

Unfortunately, the method transpose() is not natively present in Google Apps Script and this code fills the gap.

It could also be possible to code a transpose() version which alters the original object so you that you simply call cell.transpose() instead of writing cell = cell.transpose()

 

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ec6f3297-e95f-455b-9177-9f4086341f07%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/61d3ffde-ae8d-4b0a-ad56-56335ecdb28d%40googlegroups.com.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/06944a44-42f5-4b52-8237-51a256d8e6ac%40googlegroups.com.

[Michael O'Shaughnessy]

Just adding my thoughts....  Why not add a tab that has a query to get the columns you want then in your code grab the data from that tab?  You don't even need a query in the new tab, you could use curly braces {}.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHpDaiiV5yqu3%3DCPUM4Ex8PS8z%3DQA7msL%3D%2BLamTbTwxvbcs2Fw%40mail.gmail.com.

[Wilson Galter]

Hi, have been doing it just like you suggested and it worked for a while. But the webapp has multiple users so the sheet tab query or filter was getting buggy/slow when multiple users tried to use it simultaneously. 

So I decided to build something on the app-scripts backend and instead. So far this solution works supper well!

I do appreciate the input. Here is my code and I'm sure there is room for improvement!

the original array looks like this

[[1, 2, GILVANDO PEREIRA DOS SANTOS, 21/11/2019, AUTORIZADO, 100,00], [3, 1, CARMEM HELENA DE PAULA MEIRELLES, 20/11/2019, GARO, 10000000], [4, 1, CARMEM HELENA DE PAULA MEIRELLES, 20/11/2019, GARO, 10.000.000,00]]

with the code below I dropped the position number [2] (full name) counting from 0. 

Resulting=

[ [1, 2, 21/11/2019, AUTORIZADO, 100,00], [3, 1, 20/11/2019, GARO, 10000000], [4, 1, 20/11/2019, GARO, 10.000.000,00]]

And it seams fast...Well, faster than using the sheet tabs query/filter. I've tested it with over 50 users querying and submitting data to the same sheet all at once and it's holding its ground so far. 

.HTML form

    <form  method="get" name="search" class="fancy-form">

  

            <input list="funcionarioValeIDandNameList"size="50" type="text" placeholder="ID ou NOME DO FUNCIONARIO" name="funcionarioIDandNameIdSerach" id="funcionarioIDandNameIdSearch" onkeypress="if(event.keyCode=='13'){document.form1.OKSearch.focus();return false;}" >

   <datalist id="funcionarioValeIDandNameList"   >

    <option disabled selected value=""> </option>

  </datalist> 

  

  

  <input list="mon" type="text" placeholder="MES" name="month" id="month" size="2"  >

   <datalist id="mon"   >

    <option disabled selected value=""></option>

    <option>1</option>

    <option>2</option>

    <option>3</option>

    <option>4</option>

    <option>5</option>

    <option>6</option>

    <option>7</option>

    <option>8</option>

    <option>9</option>

    <option>10</option>

    <option>11</option>

    <option>12</option>

  </datalist> 

  

    <input list="y" type="text" name="year" placeholder="ANO" id="year" size="5" >

   <datalist id="y"   >

    <option disabled selected value=""> </option>

    <option>2017</option>

    <option>2018</option>

    <option>2019</option>

  </datalist> 

  <Input type="button" class="buttonGreenNavBar"value=" 🔎 " onclick="formfuncionarioValeSubmit()">

    

  </form>

<div id="HtmlTableElement1"></div>

javascript , pass form field values to appscript

      function formfuncionarioValeSubmit() {// get the search selection to filter

// startLoader();

            google.script.run.withSuccessHandler(reloadTable)// feed table with filtered results

            .testeQueryAndRemoveColumn(document.forms['search']);//get search values to appscript code
}

         function reloadTable(table) {

                 document.getElementById("HtmlTableElement1").innerHTML = table; //load table to html element

                 

                                  

}

code.gs

 function testeQueryAndRemoveColumn(fieldData){// remove column

              var funcionarioIDandNameIdSerach = fieldData.funcionarioIDandNameIdSerach.toString();

   //Logger.log(funcionarioIDandNameIdSerach);

           var funcionarioId = funcionarioIDandNameIdSerach.split(" | ")[0] // split and convert value to string. 

           var month = fieldData.month;

           var year = fieldData.year;

   

    

           var sheet = SpreadsheetApp.openById('11CrzziypWFfXREktGXA31PKJJPeGaUF_MfAn6dJhBOY').getSheetByName('employeVales');

           var lastRow = sheet.getLastRow();

           var cell = sheet.getRange(3, 1,lastRow-2,6).getValues();

     

  // Logger.log(cell)

Array.prototype.transpose = function() { var a = this; return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); }); }

//transpose the array, because getValues() returns an array of rows

cell = cell.transpose();

cell.splice(2,1); // removes the second column... count starts at 0

//if now you need an array or rows again, just transpose again

cell = cell.transpose();

//  Logger.log(cell)

   
         var data2D = cell.filter(function(item) {//filter by user Id and by spliting month and year

           var dateSplit = item[3].split('/');

        return dateSplit[1] === month && // position Month[1] of the date

               dateSplit[2] === year && // position year[2] of the date

               item[1] == funcionarioId // position funcuionario[1] of the array

});

  //Logger.log(data2D);

    var data = data2D;

  var table = "<table id='table1' border='0' ><tr> <th>Item ID</th> <th>Funcionario ID</th>  <th>DATA</th>  <th>DESCRIPTION</th>  <th>VALOR</th>  <th>TIMESTAMP</th> </tr>";

   //<td><input type='checkbox' /></td>

  for( var i=0; i<data.length; i++ ) {//rows

    table = table.concat("<tr>");

    for( var j=0; j<data[0].length; j++ ) {//column

      table = table.concat("<td>");

      table = table.concat(data[i][j].toString());

      table = table.concat("</td>");

    }  

    //table = table.concat("<td><input type='checkbox' name='check-tab1'></td></tr>");

     }

     table= table.concat("</table>");

 // Logger.log(table);

  return table;

}

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ec6f3297-e95f-455b-9177-9f4086341f07%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/61d3ffde-ae8d-4b0a-ad56-56335ecdb28d%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/06944a44-42f5-4b52-8237-51a256d8e6ac%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.