52955

Dynamic namedRange, Trying to print only cells with data in them

<h3>Question</h3>
function readNamedRange() { var app = SpreadsheetApp; var activeSheet = app.getActiveSpreadsheet().getActiveSheet(); var listDown = activeSheet.getRange("listDown"); var result = activeSheet.getRange("listDown").getValues(); }

In the above code, my active sheet contains a namedRange which has the entire B column set to the namedRange called listDown. Cells B1 to B10 have data 1 through 10 in them. I am trying to print only data cells with values in them using both for loop and an if statement inside it. I have tried the following logic given below:

for(var i=0; i <result.length; i++){ if(result.length[i] != "" && result.length[i] != undefined ){ console.log(result); }else{ console.log("namedRange is empty"); } }

My above logic is not complete and I am unable to understand how to do it.


<h3>Answer1:</h3>

I believe your goal as follows.

<ul><li>You want to show the values of cells except for the empty cells at the log.</li> </ul>

For this, how about this modification?

<h3>Modification points:</h3> <ul><li>listDown is not used in your script. </li> <li>The value retrieved by getValues() is 2 dimensional array. And your range is one column. <ul><li>In this case, each value can be retrieved by result[i][0].</li> </ul></li> <li>In your script, result.length[i] always becomes undefined.</li> <li>At console.log(result), all values of result is shown.</li> <li>In your case, result[i][0] != "" can be used for the if statement.</li> </ul>

When above points are reflected to your script, it becomes as follows.

<h3>Modified script:</h3> function readNamedRange() { var activeSheet = SpreadsheetApp.getActiveSheet(); var result = activeSheet.getRange("listDown").getValues(); for (var i = 0; i < result.length; i++) { if (result[i][0] != "") { // Modified console.log(result[i][0]); // Modified } else { console.log("namedRange is empty"); } } } <h3>References:</h3> <ul><li>getValues()</li> <li>Array</li> </ul><h2>Added:</h2> <ul><li>You want to show the values of only cells except for the empty cells.</li> </ul>

For this, how about the following modified script?

<ul><li>The script in else was removed.</li> <li>In order to reduce the process cost, var end = activeSheet.getLastRow() is used for the for loop.</li> </ul><h3>Modified script:</h3> function readNamedRange() { var activeSheet = SpreadsheetApp.getActiveSheet(); var result = activeSheet.getRange("listDown").getValues(); var end = activeSheet.getLastRow(); for (var i = 0; i < end; i++) { if (result[i][0] != "") { console.log(result[i][0]); } } }

来源:https://stackoverflow.com/questions/61906664/dynamic-namedrange-trying-to-print-only-cells-with-data-in-them

Recommend

  • Attempting to use a Template instead of an Overloaded Function in Arduino: TYPE not declared in this
  • Json.Net Deserialization Constructor vs. Property Rules
  • Reactjs - passing state value from one component to another
  • Retrofit is returning cached response
  • Parse JSON with optional field
  • Laravel: Google contacts API gives empty results
  • php using msaccess
  • In Java, how can I take a variable obtained from a user input from one method and use the output in
  • Rails 3 Full_Calendar
  • Was default_marker removed from mapbox-gl.js
  • Hibernate Idempotent Update
  • Arc gradients in Flutter?
  • Java 11 and E(fx)clipse JavaFX plugin on Eclipse 4.9: An error has occurred - see the log file
  • Threads and Concurrent Modification Exception working with a list
  • android : speech recognition what are the technologies available
  • $this->a->b->c->d calling methods from a superclass in php
  • Boolean filter using a timestamp value on a dataframe in Python
  • JQuery Mobile Ajax Navigation in Single-Page Template
  • Java Collections.shuffle() weird behaviour [closed]
  • How to create subsets of a single set of elements with XSLT?
  • xpath assertion failure with dynamic xpath
  • how to run a different select statement based on condition in Hive SQL
  • Problems to understand DXGI DirectX 11 Desktop Duplication to get a Buffer or Array
  • Android: Unable to detect vertical plane
  • Google App Engine Datastore: Dealing with eventual consistency
  • php “page caching” solution suggestions for CMS Applications
  • Debug `Unexpected end of JSON input Error` on content script
  • Apple Mach-O Linker error (“duplicate symbol”)
  • Bitrate JWplayer
  • Excel VBA : conditional formatting of sheet1 cells from sheet2 values in excel 2007
  • Codeigniniter insert data through models and controller
  • Jersey serializes character value to ASCII equivalent numeric string
  • Angular FormGroup won't update it's value immediately after patchValue or setValue
  • Firebase: How to read from external DB?
  • CAS 4 - Not able to retrieve the LDAP groups after successful authentication
  • What does the “id” field in an Android “Google Play Music” broadcast intent correspond to?
  • convert json to excel in java