I have a google sheet wherein there are 2 sheets named "Position" and "History" respectively.

I have a custom function that takes data in the "History" sheet and arrange that data in the "Position" sheet to create a portfolio of stocks with the FIFO (First in first out) method in the ticker, share and purchase column.

Now, the problem is, everything works fine until there are fewer data in the "History" sheet, and when data is increased in the "History" sheet, the "Exceeded maximum execution time (line 0)" error occurs.

So, How to fine-tune that code to full fill the task, if not what is the possible workaround.

Sample sheet link: Click to open sample sheet

function generateTrade(stockQuant, stockPrice, action){
return {
shares: stockQuant,
price: stockPrice,
action: action

function generateFifo(security, actions, quantity, price){
let portfolio = new Map();
for(var i = 0; i<security.length; i++){

let ticker = security[i].toString();
let action = actions[i].toString();
let stockQuant = Number(quantity[I]);
let stockPrice = Number(price[I]);
let trade = generateTrade(stockQuant, stockPrice, action);

if(action == "Buy" || action.toUpperCase() == "DRIP"){
let activeTrades = portfolio.get(ticker);
if(activeTrades == null){
portfolio.set(ticker, [trade]);
} else {

if(action.toUpperCase() == "SPLIT"){
let activeTrades = portfolio.get(ticker);
let ratio = quantity[i].toString().split(":");
activeTrades = activeTrades.map((trade) =>{
trade.shares = trade.shares * ratio[0];
trade.price = trade.price / ratio[0];
trade.shares = trade.shares * ratio[1];
trade.price = trade.price / ratio[1];

if(action == "Sell"){
let activeTrades = portfolio.get(ticker);
let precision = 8;
if (activeTrades != null){
let sharesToSell = Number(Number(trade.shares).toFixed(precision));
while (sharesToSell > 0){
  sharesToSell = Number(Number(sharesToSell).toFixed(precision));
  if (activeTrades.length > 0){
    let itemToSell = activeTrades[0];
    itemToSell.shares = Number(Number(itemToSell.shares).toFixed(precision));
    if(itemToSell.shares == sharesToSell){
      sharesToSell = 0;

    else if(itemToSell.shares < sharesToSell){
      sharesToSell -= itemToSell.shares;

    else {
      itemToSell.shares -= sharesToSell;
      sharesToSell = 0;

if (activeTrades.length == 0){
 return portfolio;

     function myPositions(security, actions, quantity, price){
    let portfolio = generateFifo(security, actions, quantity, price);

   let returnArry = [];
   portfolio.forEach((value, key) => {
    let shares = 0;
   let totalCost = 0;
   let avgPrice = 0;
  value.map(trade => {
   shares += trade.shares;
  totalCost += trade.shares * trade.price;

  avgPrice = totalCost / shares;
 returnArry.push([key, shares, avgPrice]);

return returnArry;

The output should come as this image: Click to open image

Hope I am clear to you, please help.


2 Answers 2


Google Sheets custom function have a 30 seconds execution time limit. If the time execution of your custom function depends on a growing data source it's very likely that it eventually will fail. To prevent this you have to change your approach.

You might use an installable trigger together with a control statement to automatically refresh the results when they should be updated.



I meet this issue also, But I fixed it now. You need to try give up use any loop like while / for And change to use foreach() It can fixed your problem maybe. I can.

  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Mar 20, 2023 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.