getValues From getRangeList

My script is running very slow because I am retrieving values from many different ranges one by one to apply a specific execution.

I have researched and found out about the getRangeList.

What I would like to do is put in an array all my

var 1 = sheet.getRange('A1:B1').getValues()
var 2 = sheet.getRange('A2:B2').getValues()
var 3 = sheet.getRange('A3:B3').getValues()

to reduce API calls, like such

var array = [1,2,3]

and then use

array[0]
array[1]
array[2] 

for specific actions.

Currently this is my code:

var ss = SpreadsheetApp.getActiveSpreadsheet();  
var sheet = ss.getSheetByName('Meta Awareness Campaign');    
var metacampaignvalues = sheet.getRangeList(['A3:AM3','A4:AM4','A5:AM5','A6:AM6','A7:AM7','A8:AM8','A9:AM9','A10:AM10','A11:AM11','A12:AM12','A13:AM13','A14,AM14','A15:AM15','A16:AM16','A17:AM17','A18:AM18','A19:AM19','A20:AM20']).getRanges().map(range => range.getValues());

So I need to use

metacampaignvalues[0] = sheet.getRange('A3:M3').getValues()

metacampaignvalues[1] = sheet.getRange('A4:M4').getValues()

metacampaignvalues[2] = sheet.getRange('A5:M5').getValues()

etc...

In its current state, my code is receiving an error saying: Exception: Range Not Found.

Thank you for your help.

I think that when I saw your script, 'A14,AM14' is required to be modified to 'A14:AM14'. I thought that this might be the reason for your current issue of Exception: Range Not Found..

When your script is modified, please modify as follows.

From:

var metacampaignvalues = sheet.getRangeList(['A3:AM3','A4:AM4','A5:AM5','A6:AM6','A7:AM7','A8:AM8','A9:AM9','A10:AM10','A11:AM11','A12:AM12','A13:AM13','A14,AM14','A15:AM15','A16:AM16','A17:AM17','A18:AM18','A19:AM19','A20:AM20']).getRanges().map(range => range.getValues());

To:

var metacampaignvalues = sheet.getRangeList(['A3:AM3', 'A4:AM4', 'A5:AM5', 'A6:AM6', 'A7:AM7', 'A8:AM8', 'A9:AM9', 'A10:AM10', 'A11:AM11', 'A12:AM12', 'A13:AM13', 'A14:AM14', 'A15:AM15', 'A16:AM16', 'A17:AM17', 'A18:AM18', 'A19:AM19', 'A20:AM20']).getRanges().map(range => range.getValues());

• But, in your script, it seems that the row numbersa are continuous numbers. So, how about the following modification?

    var metacampaignvalues = sheet.getRange('A3:AM20').getValues();
  

  • By this modification, for example, metacampaignvalues[5] returns the values of A8:AM8.

Note:

• As another approach, if you want to retrieve the values from non-continuous row numbers like 'A1:C1', 'A3:C3', 'A5:C5' with low process cost, how about using Sheets API as follows? When you test this script, please enable Sheets API at Advanced Google services.

  function sample() {
    const sheetName = 'Meta Awareness Campaign';
    const a1Notations = ['A1:C1', 'A3:C3', 'A5:C5'];
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const ranges = a1Notations.map(e => `'${sheetName}'!${e}`);
    const res = Sheets.Spreadsheets.Values.batchGet(ss.getId(), { ranges }).valueRanges.reduce((o, e) => (o[e.range.split("!")[1]] = e.values, o), {});
    console.log(res);
  }
  

  • By this sample script, you can retrieve an object as follows.

    {
      "A1:C1":[["A1","B1","C1"]],
      "A3:C3":[["A3","B3","C3"]],
      "A5:C5":[["A5","B5","C5"]]
    }