D
D
delongeroman2020-08-17 19:58:06
Google Apps Script
delongeroman, 2020-08-17 19:58:06

How to bypass the script execution limit of 6 minutes?

Here is a piece of code.

do{ 
      var fio = sheet.getRange(7+i, 8).getValue()
      var data = sheet.getRange(7+i, 1).getValue()
      var vremya = sheet.getRange(7+i, 2).getValue()
      var zakazchik = sheet.getRange(7+i, 3).getValue()
      var adres = sheet.getRange(7+i, 5).getValue()
      var dom = sheet.getRange(7+i, 6).getValue()
      var avto = sheet.getRange(7+i, 7).getValue()
      var prinyal = sheet.getRange(7+i, 11).getValue()
      var formaop = sheet.getRange(7+i, 12).getValue()
      var sdal = sheet.getRange(7+i, 13).getValue()
      var status = sheet.getRange(7+i, 14).getValue()   
      if(status ==  && formaop != ){
      zp.getRange(2+j, 1).setValue(fio)
      zp.getRange(2+j, 2).setValue(data)
      zp.getRange(2+j, 3).setValue(vremya)
      zp.getRange(2+j, 4).setValue(zakazchik)
      zp.getRange(2+j, 5).setValue(adres)
      zp.getRange(2+j, 6).setValue(dom)
      zp.getRange(2+j, 7).setValue(avto)    
      zp.getRange(2+j, 8).setValue(formaop)   
      zp.getRange(2+j, 9).setValue(prinyal)  
      zp.getRange(2+j, 10).setValue(sdal)  
      j++
      } 
      i++
  }
  while(data != "")

The code is repeated 30 times for different pages, as a result, when the script went through 13 pages, I was faced with the fact that the script did not have time to process all the pages in 6 minutes and ends before the information that needs to be transferred from all other sheets to 1 common sheet ends . Any ideas how to get around this?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Maxim Stoyanov, 2020-08-17
@delongeroman

Optimize code!
On each access to the table, you lose a lot of time getRange , getValue , setValue ... It's
best to get all the data once, process it and put it back into the table once, if the data allows it, of course.
In short:
Try to minimize the number of table/sheet accesses.
---
Maxim Stoyanov (stomaks), developer of Google Apps Script .
g-apps-script.com
stomaks.me

G
Grigory Boev, 2020-08-17
@ProgrammerForever

Wow, that's too fat.
The data must be retrieved all at once , using range.getValues() The
result is a two-dimensional array accessed data[row][column] (numbered from 0)
Add rows

let maxRows = sheet.getMaxRows();
  let maxColumns = sheet.getMaxColumns();
  let data = sheet.getRange(1, 1, maxRows, maxColumns).getValues();

Then you can start by replacing in Notepad ++
sheet\.getRange\(\s*(.*?)\s*,\s*(.*?)\s*\)\.getValue\(\)

on
inData[$1][$2]
And do the same with the output, so that the data is output to the table too at a time , using range.setValues()

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question