CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mina
Mina

Reputation: 63

Array values not recognised in google Apps Script

I'm trying to create an interactive database for my project management dashboard.

In conclusion, I have several sheets in a spreadsheet and I want to build a function that goes to a specific sheet and fetch some data and return it to the dashboard sheet. I built another function that puts all the sheets names in an array then I try to pull out the sheet name from the array and pass it to the datafetch function to go to the sheet and fetch the data. However, the datafetch function can't read the sheet name from the array. it seems like the array stores the data in a numerical format while the datafetch function needs a sheet name which is a string. that's why the error message is like

10:53:32 PM Error   
TypeError: Cannot read property 'getRange' of null
latestUpdate    @ Latest Update.gs:21

it means the sheet doesn't exist while the sheet does exist because I built other functions and they are all working.

The code is

function latestUpdate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var numOfRecords = ss.getSheetByName('Projects List').getLastRow();
  var counter;
  var records = [];
  //storing records in array
  for(counter = 1; counter < numOfRecords; counter = counter+1){

    records[counter-1] = ss.getSheetByName('Projects List').getRange(counter+1,1).getValue();

  }

  //fetching data from the sheet
  
  var counter1; //a counter;
  var lastUpdateDate;
  var updateDates = [];

  for (counter1=0; counter1 <= records.length; counter1=counter1+1){

    lastUpdateDate = ss.getSheetByName(records[counter1]).getRange(1,17).getValue(); //error happens here
    updateDates = ss.getSheetByName(records[counter1]).getRange(3,7,50).getValues(); //error happens here

  }


  Logger.log(records);
  Logger.log(lastUpdateDate);
  
}

Can you help to solve this, please?

many thanks

Upvotes: 0

Views: 526

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14537

Just in case. You can use for (element of array) loop condition instead of a counter. And you don't even need a loop to get the list of records. Your code could look like this:

function latestUpdate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rec_sheet = ss.getSheetByName('Projects List');
  var records = rec_sheet.getRange('A2:A' + rec_sheet.getLastRow()).getValues().flat();

  //fetching data from the sheet
  
  for (var rec of records) {
    var lastUpdateDate = ss.getSheetByName(rec).getRange(1,17).getValue();
    Logger.log(lastUpdateDate);

    var updateDates = ss.getSheetByName(rec).getRange(3,7,50).getValues();
    Logger.log(updateDates);
  }

}

Upvotes: 2

CMB
CMB

Reputation: 5163

Just to explain what has been answered in comments:

for (counter1=0; counter1 <= records.length; counter1=counter1+1) {}

This will execute the code block at counter1 = 0 until counter1 = records.length. However, JavaScript arrays are indexed from 0 (as the first entry), so the array indexes will go from 0 to length-1. Accessing records[length] is not a valid reference, and the getSheetName() method returns null.

Upvotes: 3

Related Questions