GScript- Optimise time of script to fetch data from one G ss to another

Question

I hope you are doing well, I have tried all related topic to find an answer to the following question but I could not find a good answer to my problem.

Here is my problem: I wrote a very simple script to fetch data from several spreadsheet into 1 but my script usually fails since it is taking too much computation" I can understand my script is not optimised but I am not sure how to proceed to avoid so much calculation {apprently the solution would be storing in an array but I do not know the best way to do that}

I would appreciate your help on that! Thanks :)

function fetchCluster14ApplicantTrackers() {



  var masterSheet1 = 'link1';
  var masterSheet2 = 'link2';
  var masterSheet3 = 'link3';
  var masterSheet4 = 'link4';

  var tagetedSheet = 'Applicant_Tracker';

  var cluster1Pipeline1 = SpreadsheetApp
  .openByUrl(masterSheet1)
  .getSheetByName(tagetedSheet);

  var cluster1Pipeline2 = SpreadsheetApp
 .openByUrl(masterSheet2)
 .getSheetByName(tagetedSheet);

  var cluster1Pipeline3 = SpreadsheetApp
  .openByUrl(masterSheet3)
  .getSheetByName(tagetedSheet);

   var cluster1Pipeline4 = SpreadsheetApp
  .openByUrl(masterSheet4)
  .getSheetByName(tagetedSheet);


  var getcluster1DataPipeline1 = 
  cluster1Pipeline1.getRange(1,3,11000,33).getValues();
  var getcluster1DataPipeline2 = 
  cluster1Pipeline2.getRange(1,3,5000,33).getValues();
  var getcluster1DataPipeline3 = 
  cluster1Pipeline3.getRange(1,3,5000,33).getValues();
  var getcluster1DataPipeline4 = 
  cluster1Pipeline4.getRange(1,3,5000,33).getValues();


  var fetchPipeline1 = 
  SpreadsheetApp.getActive().getSheetByName('pipeline1');
  var fetchPipeline2 = 
  SpreadsheetApp.getActive().getSheetByName('pipeline2');
  var fetchPipeline3 = 
  SpreadsheetApp.getActive().getSheetByName('pipeline3');
  var fetchPipeline4 = 
  SpreadsheetApp.getActive().getSheetByName('pipeline4');




    fetchPipeline1.getRange(1,10,11000,33)
    .setValues(getcluster1DataPipeline1);  

    fetchPipeline2.getRange(1,10,5000,33)
    .setValues(getcluster1DataPipeline2);  

    fetchPipeline3.getRange(1,10,5000,33)
    .setValues(getcluster1DataPipeline3);  

    fetchPipeline4.getRange(1,10,5000,33)
    .setValues(getcluster1DataPipeline4);

    }

Answer 1

In your script, each value is retrieved from 4 Spreadsheets, then they are put to 4 sheets in a Spreadsheet. I think that each function can be worked individually. So how about this workaround? In this workaround, Sheets API is used. The flow is as follows.

1. Retrieve values from 4 Spreadsheets.
2. Put the values to 4 sheets.

I would like to propose to run above flow with the asynchronous processing using the fetchAll method. This fetchAll method was added at January 19, 2018. And it has already been known that this method works by the asynchronous processing.

When you use this modified script, please enable Sheets API at API console. You can see about how to enable Sheets API at here.

Modified script:

function fetchCluster14ApplicantTrackers() {
  // Please set SpreadsheetId of source Spreadsheet.
  var sourceSpreadsheets = [
    {id: "### SpreadsheetId of masterSheet1 ###", range: "link1!C1:AI11000"},
    {id: "### SpreadsheetId of masterSheet2 ###", range: "link2!C1:AI5000"},
    {id: "### SpreadsheetId of masterSheet3 ###", range: "link3!C1:AI5000"},
    {id: "### SpreadsheetId of masterSheet4 ###", range: "link4!C1:AI5000"},
  ];

  var destinationSpreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var destinationSheets = [
    {range: "pipeline1!J1:AP11000"},
    {range: "pipeline2!J1:AP5000"},
    {range: "pipeline3!J1:AP5000"},
    {range: "pipeline4!J1:AP5000"},
  ];
  var accessToken = ScriptApp.getOAuthToken();

  // Get values.
  var requests1 = sourceSpreadsheets.map(function(e) {
    return {
      url: "https://sheets.googleapis.com/v4/spreadsheets/" + e.id + "/values/" + encodeURIComponent(e.range) + "?fields=values",
      method: "get",
      headers: {"Authorization": "Bearer " + accessToken},
    };
  });
  var values = UrlFetchApp.fetchAll(requests1);

  // Put values.
  var requests2 = destinationSheets.map(function(e, i) {
    return {
      url: "https://sheets.googleapis.com/v4/spreadsheets/" + destinationSpreadsheetId + "/values/" + encodeURIComponent(e.range) + "?valueInputOption=USER_ENTERED",
      method: "put",
      payload: values[i],
      contentType: "application/json",
      headers: {"Authorization": "Bearer " + accessToken},
    }
  });
  UrlFetchApp.fetchAll(requests2);
}

Note:

• Before you run the script, please set the variables.
  • id is the Spreadsheet ID.
• If The process was not improved using this workaround, please check this GAS library. By using this GAS library, both retrieving and putting values can be run by the asynchronous processing.

References:

• fetchAll method
• fetchAll method in UrlFetch service for Google Apps Script
• Spreadsheet ID

If this workaround was not the result you want, I'm sorry.