CODEWITHSUNDEEP
google-apps-scriptgetvalue
Shaun
Shaun

Reputation: 65

Apps script .getValue() returning #N/A

I'm having an issue with this apps script that's attempting to add data to my Google Cloud database. The problem variables are Open, High, Low, Close; which are all values found by formulas in the 'RAW' sheet. The formulas behind Open, High, Low, Close looks like this:

=round(index(B6:B35,match($A$3,$A$6:$A$35,0))/index(AUD!C:C,match(A3,AUD!A:A,0)),8)

When the query tries to execute the getValue() is returning #N/A, as is getDisplayValue(), which doesn't align with the table parameters.

The problem only occurs when the reference cells include formulas, when I change those cells to integers to test it goes through to the db no problem.

Is there a different way to do this, or stop getValue() returning #N/A?

Many thanks!

OM.

function insert() {

// RAW Variables
  var date_  = Utilities.formatDate(SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RAW').getRange('A3').getValue(), "GMT+10", "yyyy-MM-dd");
  var open_  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RAW').getRange('B3').getValue()
  var high_  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RAW').getRange('C3').getValue()
  var low_   = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RAW').getRange('D3').getValue()
  var close_ = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RAW').getRange('E3').getValue()
  var volume_= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RAW').getRange('F3').getValue()

// Connection Variables
  var connectionName = '.....';
  var user = '.....';
  var userPwd = '......';
  var db = '.....';
  var dbUrl = 'jdbc:google:mysql://' + connectionName + '/' + db;
  var conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
  var stmt = conn.createStatement()

// Query  
  var query="insert into test(Date, Open, High, Low, Close, Volume) values('"+date_+"','"+open_+"','"+high_+"','"+low_+"','"+close_+"','"+volume_+"')"

  stmt.execute(query)
  stmt.close()
  conn.close()
 }

Update:

Adding iferror() to the begging of the formulas I was able to get the error value through to the table, however that's not an ideal result for me.

The fact that getValue() or getDisplayValues() doesn't return the result of the formula is my biggest concern. What is most strange is that the right most column is also a formula: =index(F6:F35,match($A$3,$A$6:$A$35,0))

The only difference between that formula and the problem ones is a division operator '/'. Any thoughts regarding that?

Outside of testing on the insert query I've also been testing by using the Open, High.. to cell.setValue(), within sheets, and getting the same #N/A result. 

| 2018-05-13 | 11278.421244330 | 11620.21125128 | 11118.99605973 | 11554.50481772 |  5866380000 |
| 2018-05-14 | 11576.562811400 | 11799.80070418 | 11118.00969906 | 11581.46548861 |  7364150000 |
| 2018-05-15 | 11657.201395350 | 11832.62472130 | 11324.11133355 | 11396.32950125 |  6705710000 |
| 2018-05-16 |     0.000000000 |     0.00000000 |     0.00000000 |     0.00000000 |  6760220000 |
+------------+-----------------+----------------+----------------+----------------+-------------+

Result from insert query ^

Upvotes: 1

Views: 1929

Answers (2)

Lucas Dias
Lucas Dias

Reputation: 11

I had a similar problem when trying to get information from a query with importrange. I think you can't use data from an importrange as a value, though it could be the combination of have both. If your index value in the formula is an importrange data it probably is the reason.

I was trying to make a dynamic formula to paste on my sheets based on the selected column. It kept giving the #N/A while I tried to get information of the column from the importrange. As soon as I went from a match that used the importrange data to a calculated way to determine the column it worked.

Upvotes: 1

Aidan
Aidan

Reputation: 1750

Use IFERROR to eliminate N/A and #DIV/0!

=IFERROR(formula, "value to display if an error occurs")

I can't test yours but I'd say this is what you are looking for:

=IFERROR(round(index(B6:B35,match($A$3,$A$6:$A$35,0))/index(AUD!C:C,match(A3,AUD!A:A,0)),8), 0)

getValue() will now always return 0 or the result.

Upvotes: 1

Related Questions