google sheets - Spreadsheet custom function suddenly stop working - Stack Overflow

admin管理员组

文章数量:1277317

I use this to keep track of my stock portfolio, been using it for almost three years now, and recently it stop working, can't figure out why.

function MyPortfolio2(tickers, values , price) 
{
  var total = []
  var sums = {}
  var average = {}
  var total_price
  for(i=0; i < tickers.length; i++)
  {   
    var t = tickers[i].toString()

    if(t != "Cash")
    {
       if(t in sums)
       {
         if(Number(values[i])>0){
          total_price=sums[t]*average[t]+Number(values[i])*Number(price[i])
          average[t]=total_price/(sums[t]+Number(values[i]))
         }
         sums [t] += Number(values[i])
       }
       else
       {
         sums[t] = Number(values[i]) 
         average[t] = Number(price[i])
       }
    }

  }
  for(var ticker in sums)
  {
    if(sums[ticker]>0)
    {
      total.push([ticker, sums[ticker], average[ticker]])
    } 
  }
  return total
}

I have two copies and it both stop working at the same time, but if I return my spreadsheet to a previous version, it would work for about a second before showing errors again.

EDIT1:

Screenshot of my Googlesheets

This is where input my data

error

expected output

It adds up my current stock holding to show what stocks I own and how many shares, also calculate my average buy price.

I have two Googlesheets that has this same exact function, it was the first version of my portfolio tracker sheet, I only open it up to see if it was working of not, unfortunately it wasn't either.

I greatly appreciate the help, I don't really know much about coding, kind of hard for me to find the solution on my own.

EDIT2:

DATA INPUT:

here I enter my transaction data

tickers	value	price
TSLA	4	200
TSLA	1	400
GOOG	1	100
DIS	1	150
DIS	-1	100

I use this to keep track of my stock portfolio, been using it for almost three years now, and recently it stop working, can't figure out why.

function MyPortfolio2(tickers, values , price) 
{
  var total = []
  var sums = {}
  var average = {}
  var total_price
  for(i=0; i < tickers.length; i++)
  {   
    var t = tickers[i].toString()

    if(t != "Cash")
    {
       if(t in sums)
       {
         if(Number(values[i])>0){
          total_price=sums[t]*average[t]+Number(values[i])*Number(price[i])
          average[t]=total_price/(sums[t]+Number(values[i]))
         }
         sums [t] += Number(values[i])
       }
       else
       {
         sums[t] = Number(values[i]) 
         average[t] = Number(price[i])
       }
    }

  }
  for(var ticker in sums)
  {
    if(sums[ticker]>0)
    {
      total.push([ticker, sums[ticker], average[ticker]])
    } 
  }
  return total
}

I have two copies and it both stop working at the same time, but if I return my spreadsheet to a previous version, it would work for about a second before showing errors again.

EDIT1:

Screenshot of my Googlesheets

This is where input my data

error

expected output

It adds up my current stock holding to show what stocks I own and how many shares, also calculate my average buy price.

I have two Googlesheets that has this same exact function, it was the first version of my portfolio tracker sheet, I only open it up to see if it was working of not, unfortunately it wasn't either.

I greatly appreciate the help, I don't really know much about coding, kind of hard for me to find the solution on my own.

EDIT2:

DATA INPUT:

here I enter my transaction data

tickers	value	price
TSLA	4	200
TSLA	1	400
GOOG	1	100
DIS	1	150
DIS	-1	100

RESULT:

here shows my holding and my average price

=MyPortfolio2(tickers, value, price)

TICKER	SHARES	AVG_PRICE
TSLA	5	240
GOOG	1	100

• google-sheets
• google-apps-script
• stock
• custom-function
• google-finance

Share Improve this question Follow edited Feb 24 at 12:44 Wicket 38.4k99 gold badges7878 silver badges192192 bronze badges asked Feb 24 at 9:30 Liu EdLiu Ed 911 silver badge 6

• Works fine for me. – Tedinoz Commented Feb 24 at 9:35
• Try refesh. And take your time. GAS is not super fast. :) – Sangbok Lee Commented Feb 24 at 9:38
• If click on the error, a detailed error message will show up. Could you post that? – TheMaster Commented Feb 24 at 11:01
• Try to check your custom function in Google sheet and check the execution log to make sure what's an error. – Patsytalk Commented Feb 24 at 11:05
• The question is lacking the exact error message you get. Check My Executions for failed executions, and edit the question to include the exact error message you see there. – doubleunary Commented Feb 24 at 11:55

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 2

Custom functions use Apps Script, and there are daily quotas and limitations. If you exceed a quota or limitation, the function errors out. In addition, there are long-time issues such as Google sheets custom functions stuck in loading.

To avoid those issues, use a plain vanilla spreadsheet formula, like this:

=query(
  query(
    filter(hstack(Transactions!B2:B, Transactions!D2:D, Transactions!D2:D * Transactions!E2:E), Transactions!D2:D),
    "select Col1, sum(Col2), sum(Col3) / sum(Col2) group by Col1", 0
  ),
  "where Col2 <> 0 label Col1 'Ticker', Col2 'Shares', Col3 'Avg price' ", 1
)

If you have this data in the Transactions tab:

date	tickers	action	value	price
...	TSLA	...	4	200
...	TSLA	...	1	400
...	GOOG	...	1	100
...	DIS	...	1	150
...	DIS	...	-1	100

...the formula will get this result:

Ticker	Shares	Avg price
GOOG	1	100
TSLA	5	240

See query(), filter() and Quotas for Google Services.

To vote for fixing the loading issue, click the star icon ☆ in the issue tracker. Please do not comment, i.e., do not post a "me too" or "+1" reply, but just click the star icon, because comments spam everyone's inboxes. Google prioritizes issues with the most stars.

本文标签： google sheetsSpreadsheet custom function suddenly stop workingStack Overflow