How to fix the custom function in google spreadsheet when the script can't take input?

Question

I wrote a custom function from the google app script. It's supposed to take value inside from the cell and print JSON data. It succeeded in working in the backend. However, I can't put any input from the front end.

=STOCKC(AMD)

This is the custom function I want to make inside the google sheets. It's supposed to extract data from the JSON data.

    function STOCKC(symbol){
    //this is the testcase for the backend
        //var symbol = "AMD";
        symbol = encodeURI(symbol);
       var url = "https://www.alphavantage.co/query?function=TIME_SERIES_WEEKLY&symbol="+symbol+"&apikey="+api_key;
       var responseUrl = UrlFetchApp.fetch(url);
       var response = responseUrl.getContentText();
       var stockdata = JSON.parse(response);
       var closepricear = new Array();

      if(Object.keys(stockdata).length > 1){
  var help = Object.keys(stockdata)[1];
    for(var i = 0; i < Object.keys(stockdata[help]).length; i++){
      var helpVal = Object.keys(stockdata[help])[i];
      var open = Object.keys(stockdata[help][helpVal])[0];
      var close = Object.keys(stockdata[help][helpVal])[3];
      var low = Object.keys(stockdata[help][helpVal])[2];
      var volume = Object.keys(stockdata[help][helpVal])[4];
      var high = Object.keys(stockdata[help][helpVal])[1];
      var date = Object.keys(stockdata[help]);

     closepricear.push({"close":stockdata[help][helpVal][close]})
    // closepriceard.push(date)
    }
  }
       Logger.log(closepricear);
       return closepricear;
}

The logger.log(closepriear) shows the data I want but the front end won't print.

I get

reference doesn't exist

error at the front end.

This is the closepriear Logger.log() data. This is what i get when i do the backend test. It's a long list of data consist of the following.

[19-07-13 15:52:55:823 PDT] [{close=33.2100}, {close=31.5000}, {close=30.3700}, {close=29.1000}, {close=30.3600}, {close=32.4100}, {close=27.4100}, {close=26.4400}, {close=27.5000}, {close=27.9600}, {close=28.2200}, {close=27.8800}, {close=27.6800}, {close=27.8500}, {close=28.9800}, {close=25.5200}, {close=26.3700}, {close=23.2900},

Can you please provide us with a sample of data that is written into `closepricear`? It might be crucial to debug your code, judging from the code you posted, there should be no issue after TheMaster's answer is implemented — Oleg Valter is with Ukraine, Jul 13 '19 at 22:44
@OlegValter I just edited it. Let me know if you need more or if i provided the wrong thing. — jjunbbug, Jul 13 '19 at 22:58
Thanks, @jjunbbug! That's the issue, do not return `Objects` as `Array` elements - they won't be displayed, either stringify or map values as `[...,['close',33.2100],...]` - should work (not sure what the desired output is, will post an answer after further clarification) — Oleg Valter is with Ukraine, Jul 13 '19 at 23:03

score 2 · Answer 1 · answered Jul 13 '19 at 23:35

2

Problem

To understand what is going on when your custom function pastes values, you can refer to setValues() method documentation of the Range class (Google Sheets API for apps script), it works pretty much the same way - when you return values from the function, they are interpreted as a two-dimensional Array like this (single values are interpreted the same as setValue()):

//pseudo-code; [ row1: [col1,colN], rowN: [col1,colN] ]

Solutions

Reformat your Array elements as [33.2100] (if you only need values);
Use JSON.stringify() on your objects before output (if need to preserve obj);

Useful links

setValue() method reference;
setValues() method reference;

answered Jul 13 '19 at 23:35

Oleg Valter is with Ukraine

9,619
8
37
59

I've tried to set values and this is the error message. "TypeError: Cannot find function setValues in object 1123. (line 30, file "Code")" var range = closepricear.length; var plz = range.setValues(closepricear); I'm not well versed in javascript so i'm confused how i can solve this. – jjunbbug Jul 14 '19 at 00:24
@jjunbbug, sorry if my answer caused a confusion - you don't need to actually use `setValues()` method (there is a big difference between how _custom functions_ and _scripts_ operate), it is a point of reference to better understand what's causing the issue. Simply avoid outputting `{key:value}` objects and you should be fine (or stringify them before upload). If you provide the rest of the function (`if...else` statement and loop) I can show you modifications required if you experience issues – Oleg Valter is with Ukraine Jul 14 '19 at 00:45
Hey, the of JSON.stringfy() worked. Thank you so much! However, I'm still confused about how I can reformat the array. The data literally prints like the following: [{close=33.2100}, {close=31.5000}, {close=30.3700}, {close=29.1000}, {close=30.3600}, How would i approach this problem? – jjunbbug Jul 14 '19 at 06:53
Hi! I think while I was away, TheMaster addressed the second issue of how to reformat to array (basically, you'll have to modify the value used as an argument in `.push()` method) – Oleg Valter is with Ukraine Jul 14 '19 at 13:19

TheMaster · Accepted Answer · 2019-07-14T05:06:04.437

1

Issue#1:

AMD doesn't exist at the frontend. It's a #REF error(reference doesn't exist), because AMD isn't defined. If AMD is a plain string literal and not a named range, you need to quote it, just like you did in JavaScript.

=STOCKC("AMD")

Issue#2:

The return variable closepricear is a array of objects. You need to return a single value using JSON.stringify(closepricear) or a 2D array:

closepricear.push([stockdata[help][helpVal][close]])

edited Jul 14 '19 at 05:06

answered Jul 13 '19 at 21:34

TheMaster

45,448
6
62
85

I completely understand what you're talking about. The #REF sign disappeared but I still have no output coming out. Just plain blank. No error sign or anything. I've never been more confused in my life. – jjunbbug Jul 13 '19 at 22:00
@jjun Well your return value `closepricear` is just a empty array. Given your code, `Logger.log()` will also log a empty array. I assumed you had some kind of code in `//if and for loop`. If not, It's a empty array and a empty result. – TheMaster Jul 13 '19 at 22:06
I had the same suspicion as you! However, I checked the logger.log(). When i use the back end test case var symbol = "AMD"; There's full of data. And it seems like the loop succeeded in extracting the data. That's what I meant when I said it works in backend. That's why I don't think there's a problem with the loops. – jjunbbug Jul 13 '19 at 22:27

How to fix the custom function in google spreadsheet when the script can't take input?

2 Answers2

Issue#1:

Issue#2: