Improve script performance-CodePudding

I have the following script:

function test() {
  const parameters = [
    {

      url: 'https://api.x.immutable.com/v1/orders?buy_token_address=0xccc8cb5229b0ac8069c51fd58367fd1e622afd97&direction=asc&order_by=buy_quantity&page_size=9&sell_metadata={"proto":["189"],"quality":["Meteorite"]}&sell_token_address=0xacb3c6a43d15b907e8433077b6d38ae40936fe2c&status=active',
      cell: 'Sheet1!A1',
    },
    { 
      url: 'https://api.x.immutable.com/v1/orders?buy_token_address=0xccc8cb5229b0ac8069c51fd58367fd1e622afd97&direction=asc&order_by=buy_quantity&page_size=9&sell_metadata={"proto":["190"],"quality":["Meteorite"]}&sell_token_address=0xacb3c6a43d15b907e8433077b6d38ae40936fe2c&status=active',
      cell: 'Sheet1!A12',
    },
    ];
  const ss = SpreadsheetApp.getActive();
  parameters.forEach(spec => {
    const data = ImportJSON(spec.url);
    ss.getRange(spec.cell)
      .offset(0, 0, data.length, data[0].length)
      .setValues(data);
  });
}

Url: Every URL contains a basic structure link with only difference of an ID. For example: https://puu.sh/IWYZO/fecd1e1c3b.png

Cell: Every cell is pasted 11 rows from the previous one. For example: Sheet1!A1, Sheet1!A!12, Sheet1!A23, Sheet1!A34 etc.

Problem: Right now the app is very slow and sometimes i get a timeout error because of 6 minutes limit, so I tried to split all calls into different script files but I was wondering if there is a better way in order to make this script performs faster and better.

CodePudding user response：

As noted by doubleunary's answer, most of the bottleneck is coming from the multiple API calls to immutable.com. Try to plug the endpoints into something like Reqbin and you'll see that each call takes about 1.3 seconds with some peaks of 2(!) seconds. That quickly adds up.

There's one thing that you can optimize in your code, though, which you may want to keep in mind in other situations like this.

Your code is running setValues() each time you call the API. This means that every iteration in the forEach() loop writes 11 rows to the sheet. It would be better to just build an array with all the rows and then use setValues() to write them all at once.

In your code it might look something like this:

function test() {
  //you don't need the cell on each URL, just specify the starting point
  let startcell = "Sheet1!A1"
  const parameters = [
    {
      url: 'https://api.x.immutable.com/v1/orders?buy_token_address=0xccc8cb5229b0ac8069c51fd58367fd1e622afd97&direction=asc&order_by=buy_quantity&page_size=9&sell_metadata={"proto":["189"],"quality":["Meteorite"]}&sell_token_address=0xacb3c6a43d15b907e8433077b6d38ae40936fe2c&status=active'
    },
      url: 'https://api.x.immutable.com/v1/orders?buy_token_address=0xccc8cb5229b0ac8069c51fd58367fd1e622afd97&direction=asc&order_by=buy_quantity&page_size=9&sell_metadata={"proto":["190"],"quality":["Meteorite"]}&sell_token_address=0xacb3c6a43d15b907e8433077b6d38ae40936fe2c&status=active',
    ];

  const ss = SpreadsheetApp.getActive();
  let data = []
  parameters.forEach(spec => {  
    data.push(ImportJSON(spec.url));
  });

  data = data.flat()
  ss.getRange(startcell)
      .offset(0, 0, data.length, data[0].length)
      .setValues(data);
}

Note that this will only shave off a few seconds, which isn't much compared to the real issue with the overhead caused by the API. If you find a better way to retrieve the data from Immutable you probably wouldn't even need to do this, but it's still worth knowing for your future projects and it's in Google's recommended practices.

CodePudding user response：

Based on what you said at Evaluate ImportJSON() custom function less often, the root cause of the issue is that you have many separate URLs that you want to fetch.

The optimal solution would be to find an URL endpoint that would you get all the data with one fetch. You may want to study the API you are using in more detail to find whether they have an endpoint that lets you submit multiple IDs in one call.

Also see Immutable's recommended best practices.