CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Einstein8032
Einstein8032

Reputation: 13

Create a variable with the name of a value in an array

I have the following problem and couldn't solve it, even after researching the issue. I need to to the following in a Google Apps Script

I have an array based on a spreadsheet row: 

var columnTitleValues = rangeTitle.getValues();

I save the title of each column in an array.

Now, I want to save the index of each title in the corresponding variable.

For example a spreadsheet could look like this:

**ID | Title | Date | User**

**01 | Test  | 23.5 | admin**

I have the array ["ID", "Title", "Date", "User"] and I would like to have variables created using those names, basically, I don't want to do it by hand!

var id = ..

var title = ..

var date = ..

var user = ..

Once the variable is created it will be easy to find the index of the column where the string matches, but I would like to have the names automatically.

Any clues on how I can do it?

I tried:

var columnTitleValues [0][0] = --> ERROR
eval("var columnTitleValues[0][k]=3;"); --> ERROR

I am grateful for any advice on this!

Upvotes: 1

Views: 1574

Answers (2)

Serge insas
Serge insas

Reputation: 46792

The object approach is quite seducing and reveals that user400514 is probably quite comfortable with JavaScript (and I voted up his answer) but there is also the "pure array" approach that has some attractive aspects too ;)

Here is an implementation of the array approach, keep in mind that arrays are indexed from 0 so column A is index 0 and row 1 is also index 0.

function createVars(){
  var ss =  SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var data = sheet.getDataRange().getValues();
  var headers = data.shift();// this gets the headers and removes it from data
  Logger.log(headers);
  for(var n in data){
    var id = data[n][0];
    var title = data[n][1];
    var date = data[n][2];
    var user = data[n][3];
    Logger.log('id = %s, title = %s,  date = %s, user = %s',id,title,date,user);
  }
}

Upvotes: 1

bruce
bruce

Reputation: 1408

Instead of trying to make discrete variable names, you should make the column headings into properties of an object which store their position. Here's an example. 

function headingTest() {
  var ss =  SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var data = sheet.getDataRange().getValues();

  // your titles will be in row 0
  var headings = {};
  data[0].forEach( function (cell,i) {
    headings[cell] = i;
  });

  // you can now access data by name .. for example log all the users..
  data.forEach ( function (row,i) {
    if (i > 0 ) Logger.log (row[headings['User']]);
  });


}

Upvotes: 1

Related Questions