Javascript - Insert values in google sheet cells using iteration via loop

GMadhabanandaPatra
August 23, 2023
265 views
0 votes
2 Answers

I have a array of strings.
let’s say

My problem statement is Iterate over each value of this array and call api call and write response of each data in google sheet simultaneously.

let input_data=["value1","value2",...... , "value30"]
var mySheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getActiveSheet();

for (let [idx, each] of input_data.entries()) {
   // this is a Asynchronous function is basically take input and get response from api using UrlFetchApp and write in google sheet row
    InsertApiResponseIntoCell(each,mySheet) 
}

So requirement is whichever gets first response from server will written in google sheet.
But problem is each api call pauses the task and wait for complete , so technically not behaving Asynchronous. After all operation all the datas are written in cells

How can i get response from api call and write it in cell rows while iterating over loop?

I have tried this one

for more context

async function insertApiResponseIntoCell(input, sheet) {
  var apiUrl = 'YOUR_API_URL_HERE'; // Replace with the actual API URL

  var response = UrlFetchApp.fetch(apiUrl);
  var responseBody = response.getContentText();
  
  // Parse the response and extract the data you need
  var responseData = JSON.parse(responseBody);
  
  var lastRow = sheet.getLastRow() + 1;
  var targetRange = sheet.getRange(lastRow, 1, 1, responseData.length);
  
  // Write the response data to the target range
  targetRange.setValues([[responseData]]);
}

Answers

- Cooper
- August 23, 2023 at 6:44 pm
- 0 votes
0
```
function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const ar = ["Architect.","Restore.","Content rail extension.","Apparent.","At.","Second."];//array of string
  const rgl = sh.getRangeList(["A1","B2","C3","D4","E5","F6"]);
  rgl.getRanges().forEach((r,i) => {
    r.setValue(ar[i])
  });
}
```
- Sheet.getRangeList()
Sheet0:

A B C D E F

1 Architect.

2 Restore.

3 Content rail extension.

4 Apparent.

5 At.

6 Second.
Login or Signup to reply.

- vishal_g
- August 23, 2023 at 6:52 pm
- 0 votes
0
It looks like you’re dealing with an asynchronous behavior in your loop due to the API calls. Since UrlFetchApp.fetch is synchronous and blocks the execution until it gets a response, you won’t achieve true parallel asynchronous behavior with this approach.

However, you can achieve a sort of concurrency by using UrlFetchApp.fetchAll method, which allows you to make multiple API requests concurrently. Here’s how you can modify your code to use it:
```
var input_data = ["value1", "value2", ..., "value30"];
var mySheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getActiveSheet();

function insertApiResponseIntoCell(input) {
  var apiUrl = 'YOUR_API_URL_HERE'; // Replace with the actual API URL
  var response = UrlFetchApp.fetch(apiUrl);
  return response.getContentText();
}

function writeToSheet(responseData, rowIndex) {
  var lastRow = rowIndex + 1;
  var targetRange = mySheet.getRange(lastRow, 1);
  targetRange.setValue(responseData);
}

function processApiCalls() {
  var promises = input_data.map(function (value, index) {
    return insertApiResponseIntoCell(value)
      .then(function (responseData) {
        writeToSheet(responseData, index);
      });
  });

  // Wait for all promises to resolve
  Promise.all(promises)
    .then(function () {
      Logger.log("All API calls are completed.");
    })
    .catch(function (error) {
      Logger.log("Error: " + error);
    });
}

// Call the function to start processing API calls
processApiCalls();
```
Please note:

insertApiResponseIntoCell returns a Promise after fetching the API response.
We’re using .map to create an array of promises, each representing an API call.
Promise.all waits for all promises to resolve before logging a message.
This approach doesn’t guarantee exact parallel execution, but it does enable concurrent API requests, which should help speed up the process.
Login or Signup to reply.