Optimizing a complex Google Sheets script for faster execution

[Saher Naji]

Hello Google Apps Script community,

I have a complex script that currently takes about 32 seconds to run. It handles various operations in Google Sheets, including updating multiple sheets based on user edits, applying formulas, and managing data validation rules. The script includes functions like updateQuoteDetail(), updateQuoteSummary(), copyAllQuoteData(), and hideOrShowSpecificSheets().
I'm looking for advice on how to optimize this script to reduce its execution time.

I'm open to restructuring the code if it leads to significant performance improvements. Any insights or suggestions would be greatly appreciated!

Thank you in advance for your help!

Best Regards,

Saher

This is the script:

// MAIN EVENT HANDLER
function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var sheetName = sheet.getName();
  var column = range.getColumn();
  var row = range.getRow();
 
  if (sheetName === "Quote Worksheet" && row >= 14 && row <= 121) {
    switch (column) {
      case 6: // Column F
        handleColumnFEdit(sheet, range);
        applyFormulas(sheet, row);
        break;
      case 7: // Column G
        handleColumnGEdit(sheet, range);
        break;
      case 8: // Column H
        handleColumnHEdit(sheet, range);
        break;
      case 9: // Column I
        handleColumnIEdit(sheet, range);
        break;
    }
  }
}

function applyFormulas(sheet, row) {
  sheet.getRange(row, 15).setFormula('=IF(AND(F' + row + '="Rod Set (9)", K' + row + '>0), K' + row + '*$Q$5, "")');
  sheet.getRange(row, 16).setFormula('=IF(AND(F' + row + '="Curtain Case (10)", K' + row + '>0), K' + row + '*$Q$6, "")');
  sheet.getRange(row, 17).setFormula('=IF(AND(F' + row + '="Install Each (1)", K' + row + '>0), K' + row + '*$Q$7, "")');
  sheet.getRange(row, 18).setFormula('=IF(LEFT(B' + row + ', 1) = "Q", SUM(O' + row + ':Q' + row + '), "")');
}

// HANDLER FUNCTIONS FOR COLUMN EDITS
function handleColumnFEdit(sheet, range) {
  var listsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lists");
  var startRow = range.getRow();
  var values = range.getValues();

  var dataValidationConfigs = {
    "Rod Set (9)": {
      G: listsSheet.getRange("J2:J45").getValues().flat(),
      H: ["Universal", "Small", "Large"],
      L: ["Inpro", "Track 2", "Track 3", "Track 4", "Track 5"],
      M: ["Tile", "Drywall", "Suspended"]
    },
    "Curtain Case (10)": {
      G: ["Disposable", "Reusable"]
    },
    "Install Each (1)": {
      I: listsSheet.getRange("H2").getValue()
    }
  };

  values.forEach((valueArr, i) => {
    var row = startRow + i;
    var value = valueArr[0];
   
    sheet.getRange("G" + row + ":M" + row).clearDataValidations().clearContent();

    if (dataValidationConfigs[value]) {
      Object.keys(dataValidationConfigs[value]).forEach(col => {
        var range = sheet.getRange(col + row);
        if (Array.isArray(dataValidationConfigs[value][col])) {
          var rule = SpreadsheetApp.newDataValidation()
            .requireValueInList(dataValidationConfigs[value][col], true)
            .build();
          range.setDataValidation(rule);
        } else {
          range.setValue(dataValidationConfigs[value][col]);
        }
      });
    }
  });
}

function handleColumnGEdit(sheet, range) {
  var listsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lists");
  var startRow = range.getRow();
  var values = range.getValues();

  values.forEach((valueArr, i) => {
    var row = startRow + i;
    var value = valueArr[0];
    var columnIRange = sheet.getRange("I" + row);

    columnIRange.clearDataValidations().clearContent();

    if (value === "None") {
      columnIRange.setValue("None");
    } else if (sheet.getRange("F" + row).getValue() === "Curtain Case (10)") {
      if (value === "Disposable") {
        var ruleI = SpreadsheetApp.newDataValidation().requireValueInList(listsSheet.getRange("L2:L23").getValues().flat(), true).build();
        columnIRange.setDataValidation(ruleI);
      } else if (value === "Reusable") {
        var ruleI = SpreadsheetApp.newDataValidation().requireValueInList(listsSheet.getRange("M2:M23").getValues().flat(), true).build();
        columnIRange.setDataValidation(ruleI);
      }
    }
  });
}


function handleColumnHEdit(sheet, range) {
  var listsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lists");
  var startRow = range.getRow();
  var values = range.getValues();
  var listValues = listsSheet.getRange("J2:J45").getValues().flat();

  values.forEach((valueArr, i) => {
    var row = startRow + i;
    var value = valueArr[0];
    var columnGValue = sheet.getRange("G" + row).getValue();

    if (value === "None" || columnGValue === "None") {
      sheet.getRange("I" + row).setValue("None");
    } else if (listValues.includes(columnGValue)) {
      var columnKValue = listsSheet.getRange("K" + (listValues.indexOf(columnGValue) + 2)).getValue();
      var prefix = (value === "Small") ? "A-S" : (value === "Large") ? "A-L" : "A-U";
      sheet.getRange("I" + row).setValue(prefix + columnKValue);
    } else if (value === "Custom") {
      sheet.getRange("I" + row).setValue("Custom");
    }
  });
}

function handleColumnIEdit(sheet, range) {
  var listsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lists");
  var startRow = range.getRow();
  var values = range.getValues();

  values.forEach((valueArr, i) => {
    var row = startRow + i;
    var value = valueArr[0];
    var columnJRange = sheet.getRange("J" + row);

    columnJRange.clearDataValidations().clearContent();

    if (value && sheet.getRange("F" + row).getValue() === "Curtain Case (10)") {
      var listValuesJ = listsSheet.getRange("G2:G23").getValues().flat();
      var ruleJ = SpreadsheetApp.newDataValidation().requireValueInList(listValuesJ, true).build();
      columnJRange.setDataValidation(ruleJ);
    }
  });
}

// MANUAL FUNCTIONS

function runAllFunctions() {
  // Run the three functions
  updateQuoteDetail();
  updateQuoteSummary();
  copyAllQuoteData();
  hideOrShowSpecificSheets();
 
  // Go to the next unhidden sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var currentSheet = spreadsheet.getActiveSheet();
  var foundNextSheet = false;
 
  // Loop through sheets to find the next unhidden one
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetId() === currentSheet.getSheetId()) {
      // If current sheet is found, set flag to start looking for the next sheet
      foundNextSheet = true;
    } else if (foundNextSheet && !sheets[i].isSheetHidden()) {
      // If next sheet is found and it's not hidden, activate it
      spreadsheet.setActiveSheet(sheets[i]);
      return; // Exit function after setting the active sheet
    }
  }
 
  // If no next sheet found, show an alert
  SpreadsheetApp.getUi().alert('No next unhidden sheet found.');
}

// Update "Quote Detail" sheet

function updateQuoteDetail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Quote Worksheet");
  var targetSheet = ss.getSheetByName("Quote Detail");

  // Clear previous data and reset formatting in "Quote Detail" sheet
  var rangeToClear = targetSheet.getRange("B14:R");
  rangeToClear.clearContent();
  rangeToClear.breakApart(); // Unmerge all cells in the range

  // Apply default alignment settings
  var allRange = targetSheet.getRange("B14:R122");
  allRange.setHorizontalAlignment("left");
  targetSheet.getRange("K14:K122").setHorizontalAlignment("center");
  targetSheet.getRange("O14:R122").setHorizontalAlignment("right");

  // Get the range with data from source sheet
  var sourceRange = sourceSheet.getRange("B14:N119");
  var sourceValues = sourceRange.getValues();
 
  var currentRow = 14;
  var groupStartRow = null;
  var currentGroup = null;
  var lastSubtotalRow = null;

  // Loop through source values to identify unique groups and copy data
  for (var i = 0; i < sourceValues.length; i++) {
    var value = sourceValues[i][0];

    // Ignore rows without real values (empty cells or cells with just validation lists)
    if (!value) continue;

    if (value !== currentGroup) {
      if (currentGroup !== null) {
        // Add subtotal row for the previous group
        lastSubtotalRow = targetSheet.getLastRow() + 1;
        var subtotalRange = targetSheet.getRange("B" + lastSubtotalRow + ":N" + lastSubtotalRow);
        subtotalRange.mergeAcross(); // Merge cells from B to N

        // Get the corresponding value from columns B and C for the subtotal row label
        var quoteNumber = sourceSheet.getRange("B" + (14 + groupStartRow)).getValue();
        var floorNumber = sourceSheet.getRange("C" + (14 + groupStartRow)).getValue();
        var subtotalLabel = "Quote " + quoteNumber + " Floor " + floorNumber;

        subtotalRange.setValue(subtotalLabel);

        // Add subtotal formulas for columns O, P, and Q
        setSubtotalFormulas(lastSubtotalRow, currentRow, targetSheet);

        // Move to next group
        currentRow = lastSubtotalRow + 1;
      }
      // Update current group
      currentGroup = value;
      groupStartRow = i;
    }

    // Copy the current row from B to R
    if (i === sourceValues.length - 1 || sourceValues[i + 1][0] !== currentGroup) {
      var numRows = i - groupStartRow + 1;
      var dataRange = sourceSheet.getRange("B" + (14 + groupStartRow) + ":R" + (14 + i));
      var targetRange = targetSheet.getRange("B" + currentRow + ":R" + (currentRow + numRows - 1));
      dataRange.copyTo(targetRange, {contentsOnly: true});
    }
  }

  // Handle the last group’s subtotal
  if (currentGroup !== null) {
    lastSubtotalRow = targetSheet.getLastRow() + 1;
    var subtotalRange = targetSheet.getRange("B" + lastSubtotalRow + ":N" + lastSubtotalRow);
    subtotalRange.mergeAcross(); // Merge cells from B to N

    // Get the corresponding value from columns B and C for the subtotal row label
    var quoteNumber = sourceSheet.getRange("B" + (14 + groupStartRow)).getValue();
    var floorNumber = sourceSheet.getRange("C" + (14 + groupStartRow)).getValue();
    var subtotalLabel = "Quote " + quoteNumber + " Floor " + floorNumber;

    subtotalRange.setValue(subtotalLabel);

    // Add subtotal formulas for columns O, P, and Q
    setSubtotalFormulas(lastSubtotalRow, currentRow, targetSheet);
  }

  // Add TOTAL row directly after the final subtotal row
  var totalRow = lastSubtotalRow + 1;
  var totalRange = targetSheet.getRange("B" + totalRow + ":N" + totalRow);
  totalRange.mergeAcross(); // Merge cells from B to N
  totalRange.setValue("TOTAL");

  // Add black thin border to TOTAL row
  totalRange.setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.SOLID);

  // Add subtotal formulas for columns O, P, Q, and R in TOTAL row
  setTotalFormulas(totalRow, targetSheet);

  // Apply thin black borders to the entire range B14:R122
  var finalRange = targetSheet.getRange("B14:R122");
  finalRange.setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.SOLID);
}

// Helper function to set subtotal formulas
function setSubtotalFormulas(lastSubtotalRow, currentRow, targetSheet) {
  targetSheet.getRange("O" + lastSubtotalRow).setFormula("=SUBTOTAL(9, O" + currentRow + ":O" + (lastSubtotalRow - 1) + ")");
  targetSheet.getRange("P" + lastSubtotalRow).setFormula("=SUBTOTAL(9, P" + currentRow + ":P" + (lastSubtotalRow - 1) + ")");
  targetSheet.getRange("Q" + lastSubtotalRow).setFormula("=SUBTOTAL(9, Q" + currentRow + ":Q" + (lastSubtotalRow - 1) + ")");
  targetSheet.getRange("R" + lastSubtotalRow).setFormula("=O" + lastSubtotalRow + " + P" + lastSubtotalRow + " + Q" + lastSubtotalRow);
}

// Helper function to set total formulas
function setTotalFormulas(totalRow, targetSheet) {
  targetSheet.getRange("O" + totalRow).setFormula("=SUBTOTAL(9, O14:O" + (totalRow - 1) + ")");
  targetSheet.getRange("P" + totalRow).setFormula("=SUBTOTAL(9, P14:P" + (totalRow - 1) + ")");
  targetSheet.getRange("Q" + totalRow).setFormula("=SUBTOTAL(9, Q14:Q" + (totalRow - 1) + ")");
  targetSheet.getRange("R" + totalRow).setFormula("=O" + totalRow + " + P" + totalRow + " + Q" + totalRow);
}

// Update "Quote Summary" sheet

function updateQuoteSummary() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var detailSheet = ss.getSheetByName("Quote Detail");
  var summarySheet = ss.getSheetByName("Quote Summary");

  // Clear previous data in "Quote Summary"
  summarySheet.getRange("B14:G").clearContent();

  // Get data from "Quote Detail"
  var detailRange = detailSheet.getRange("B14:K" + detailSheet.getLastRow());
  var detailValues = detailRange.getValues();

  // Prepare a map to group rows by unique combinations of B, F, I, J, K
  var summaryMap = {};

  // Process each row of data
  for (var i = 0; i < detailValues.length; i++) {
    var row = detailValues[i];

    // Extract values from "Quote Detail"
    var columnB = row[0];  // Column B in "Quote Detail" -> Column B in "Quote Summary"
    var columnF = row[4];  // Column F in "Quote Detail" -> Column C in "Quote Summary"
    var columnI = row[7];  // Column I in "Quote Detail" -> Column D in "Quote Summary"
    var columnJ = row[8];  // Column J in "Quote Detail" -> Column E in "Quote Summary"
    var columnK = row[9];  // Column K in "Quote Detail" -> Column F in "Quote Summary"
   
    // Calculate the sum of columns O, P, Q
    var columnO = detailSheet.getRange("O" + (14 + i)).getValue(); // Column O value for current row
    var columnP = detailSheet.getRange("P" + (14 + i)).getValue(); // Column P value for current row
    var columnQ = detailSheet.getRange("Q" + (14 + i)).getValue(); // Column Q value for current row
    var sum = (columnO || 0) + (columnP || 0) + (columnQ || 0);

    // Create a unique key for grouping
    var key = columnB + "|" + columnF + "|" + columnI + "|" + columnJ;

    if (summaryMap[key]) {
      // If the key exists, update the quantity (Column F) and sum (Column G)
      summaryMap[key][4] += columnK;  // Sum the quantity
      summaryMap[key][5] += sum;      // Sum the total
    } else {
      // If the key does not exist, create a new entry
      summaryMap[key] = [columnB, columnF, columnI, columnJ, columnK, sum];
    }
  }

  // Convert the map to an array for writing to the sheet
  var summaryData = Object.values(summaryMap);

  // Write the summary data to "Quote Summary"
  if (summaryData.length > 0) {
    summarySheet.getRange("B14").offset(0, 0, summaryData.length, 6).setValues(summaryData);
  }
}

// SHEETS

function copyAllQuoteData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var summarySheet = ss.getSheetByName('Quote Summary');
  var data = summarySheet.getRange('B14:B').getValues(); // Get data from column B

  // Clear previous content and borders in the destination sheets
  var sheetNames = [
    "Quote (1)", "Quote (2)", "Quote (3)",
    "Quote (4)", "Quote (5)", "Quote (6)",
    "Quote (7)", "Quote (8)", "Quote (9)",
    "Quote (10)", "Quote (11)", "Quote (12)",
    "Quote (13)", "Quote (14)", "Quote (15)",
    "Quote (16)"
  ];

  sheetNames.forEach(sheetName => {
    var sheet = ss.getSheetByName(sheetName);
    if (sheet) {
      var rangeToClear = sheet.getRange('B20:F39');
      rangeToClear.clearContent(); // Clear only the content
      rangeToClear.setBorder(false, false, false, false, false, false); // Clear borders
      rangeToClear.setBackground(null); // Clear fill color, keeping conditional formatting
    }
  });

  // Loop through the data and copy to the respective sheets
  var destinationRow = {}; // To keep track of the next row for each destination sheet

  for (var i = 0; i < data.length; i++) {
    var quoteNum = data[i][0];
    if (quoteNum && !isNaN(quoteNum) && quoteNum > 0 && quoteNum <= 16) {
      var destinationSheet = ss.getSheetByName('Quote (' + quoteNum + ')');
      if (!destinationSheet) continue; // Skip if the sheet doesn't exist

      // Initialize the row counter for the destination sheet if not already done
      if (!destinationRow[quoteNum]) destinationRow[quoteNum] = 20; // Start from row 20

      // Get the corresponding row from columns C to G in the summary sheet
      var sourceRow = summarySheet.getRange(i + 14, 3, 1, 5).getValues()[0]; // Columns C to G

      // Set the values in the destination sheet
      destinationSheet.getRange('B' + destinationRow[quoteNum] + ':F' + destinationRow[quoteNum]).setValues([sourceRow]);
      destinationSheet.getRange('B' + destinationRow[quoteNum] + ':F' + destinationRow[quoteNum]).setBorder(true, true, true, true, true, true);
     
      // Increment the destination row for the next entry
      destinationRow[quoteNum]++;
     
      // Check if the next row belongs to a different group
      if (i + 1 < data.length && data[i + 1][0].toString().startsWith('Quote')) {
        var subtotalLink = data[i + 1][0]; // Get the subtotal link from column B
        var totalValue = summarySheet.getRange(i + 15, 7).getValue(); // Get the total value from column G
       
        // Place subtotal link in column B and total value in column F
        destinationSheet.getRange('B' + destinationRow[quoteNum]).setValue(subtotalLink);
        destinationSheet.getRange('F' + destinationRow[quoteNum]).setValue(totalValue);
        destinationSheet.getRange('B' + destinationRow[quoteNum] + ':F' + destinationRow[quoteNum]).setBorder(true, true, true, true, true, true); // Set border for the subtotal row
        destinationRow[quoteNum]++; // Move to the next row for future entries
      }
    }
  }
}

function hideOrShowSpecificSheets() {
  const sheetNames = [
    "Quote (1)", "Quote (2)", "Quote (3)",
    "Quote (4)", "Quote (5)", "Quote (6)",
    "Quote (7)", "Quote (8)", "Quote (9)",
    "Quote (10)", "Quote (11)", "Quote (12)",
    "Quote (13)", "Quote (14)", "Quote (15)",
    "Quote (16)"
  ];
 
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  sheetNames.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    if (sheet) {
      const cellValue = sheet.getRange('B20').getValue(); // Check cell B20
     
      if (cellValue) {
        sheet.showSheet();
      } else {
        sheet.hideSheet();
      }
    }
  });
}

[TempleSnr]

this is something that the latest versions of Gemini can help with. I would recommend that you should use Gemini 1.5 or later and ask pertinent questions, it can be very helpful (but you'll still have to make sure the code looks right, and I would suggest you should go function by function, building slowly)

[DimuDesigns]

The following  guide on best practices should help:

https://developers.google.com/apps-script/guides/support/best-practices

[Upper SoftChaseWell]

Ir para oConteúdo 1Ir para aPágina Inicial 2Ir para o menu deNavegação 3Ir para aBusca 4Ir para oMapa do site 5

Portal Gov.brMinistério da Gestão e da Inovação em Serviços Públicos

• Órgãos do Governo
 
• Acesso à Informação
 
• Legislação
 
• Acessibilidade

• Mudar para o modo de alto contraste

Entrarcom o gov.br

Abrir menu principal de navegação

Instituto Nacional de Tecnologia da Informação

Você está aqui: Página Inicial Assuntos  Repositório  AC PR

AC PR

Compartilhe:  Compartilhe por Facebook Compartilhe por Twitter Compartilhe por LinkedIn Compartilhe por WhatsApplink para Copiar para área de transferência

Publicado em 14/07/2020 15h56 Atualizado em 30/06/2023 10h30

Autoridade Certificadora da Presidência da República de 1º nível

Cadeia v0

Autoridade certificadora: AC Presidência da República v1
Emitido em: 20/09/2006
Expira em: 20/09/2011
Tipos de certificados emitidos: Certificado de AC
Situação: expirado

Cadeia v1

Autoridade certificadora: AC Presidência da República v2
Emitido em: 13/10/2008
Expira em: 13/10/2018
Tipos de certificados emitidos: A1
Situação: expirado

Cadeia v2

Autoridade certificadora: AC Presidência da República v3
Emitido em: 22/11/2011
Expira em: 22/11/2021
Tipos de certificados emitidos: Certificado de AC
Situação: expirado

Autoridade certificadora: AC Presidência da República v4
Emitido em: 05/07/2013
Expira em: 21/06/2023
Tipos de certificados emitidos: A1, A3
Situação: expirado
Download

Cadeia v5

Autoridade certificadora: AC Presidência da República v5
Emitido em: 21/06/2018
Expira em: 02/03/2029
Tipos de certificados emitidos: A1, A3
Situação: válido
Download

Compartilhe:  Compartilhe por Facebook Compartilhe por Twitter Compartilhe por LinkedIn Compartilhe por WhatsApplink para Copiar para área de transferência

Serviços que você acessou

• SetembroCadastrar procuração digital

• Assuntos
  • Auditoria ICP-Brasil
  • Cadastro de Agente de Registro - CAR
  • Certificado Digital
  • Comitê Gestor
  • Consulta Pública
  • Credenciamento
  • Fiscalização
  • Homologação
  • ICP-Brasil
  • Legislação
  • Notícias
  • Protocolo Digital ITI
  • Publicações Técnicas
  • Repositório
  • Navegadores
  • Carteira de Identidade Nacional
  • Assinatura eletrônica avançada
• Acesso à Informação
  • Institucional
  • Ações e Programas
  • Participação Social
  • Auditorias
  • Convênios e Transferências
  • Receitas e Despesas
  • Licitações e Contratos
  • Servidores ITI
  • Informações Classificadas
  • Serviço de Informação ao Cidadão - SIC
  • Perguntas Frequentes
  • Dados Abertos
  • Sanções Administrativas
  • Ferramentas e aspectos tecnológicos
  • Agendas do ITI
  • Privacidade e Proteção de Dados
  • Programa de Gestão e Desempenho - PGD do ITI
• Centrais de Conteúdo
  • Aplicativos
  • Glossário
  • Opinião do Diretor Presidente
  • Artigos
• Canais de Atendimento
  • Imprensa
  • Ouvidoria
  • Serviço de Informação ao Cidadão - SIC
  • Trabalhe conosco
• Redes Sociais/Canais
  • Facebook
  • Twitter
  • Linkedin
  • Instagram
  • Youtube
  • Flickr
  • Telegram
• Certforum

Redefinir Cookies

Redes sociais

• Twitter
 
• YouTube
 
• Facebook
 
• Flickr
 
• Instagram

Todo o conteúdo deste site está publicado sob a licença Creative Commons Atribuição-SemDerivações 3.0 Não Adaptada.

Voltar ao topo da página

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/97b5717f-c8bb-41e0-930a-205b305ba285n%40googlegroups.com.

[Upper SoftChaseWell]

Vagas.com

 Ajuda

Verifique seu e-mail e cadastre uma nova senha

Foi enviado para seu e-mail fg***@gmail.com cadastrado no VAGAS.com.br, um link para criar uma nova senha.

---

Não tem mais acesso ao e-mail cadastrado?

Não recebeu o e-mail?

• Pode demorar alguns minutos para você receber o e-mail
• Verifique sua caixa de spam

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c86560cd-79ea-4697-b5ee-dbf3494bfe08n%40googlegroups.com.

[George Ghanem]

Hi Saher,

Optimizing code to run faster is mostly focused on eliminating as many calls to Google services as possible. Each call to a google service could take 1-3 seconds and if you have many of them, they add up and cause the lengthy execution time.

In your case, I see that you only have 1 for loop that I can see. So you may gain the most efficiency by focusing on reducing time taken in that for loop.

Try to remove all getValues() and setValues() calls from that loop. Inside the for loop, store the updated values in an array and just write the array back to the spreadsheet at end of for loop.

I can help you with that if you are unsure how to do it.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/97b5717f-c8bb-41e0-930a-205b305ba285n%40googlegroups.com.

[Aakash Yadav]

could u provide google sheet , there code is runnng

--