JavaScript function in Google Apps Script cannot read properly one of my input variables (Uncaught error)

Viewed 54

I have a number of Google spreadsheets that I am handling with Apps Script but for simplicity let's assume that we are only talking about four spreadsheets named 'Spreadsheet1', 'Spreadsheet2', 'Spreadsheet3'. 'Spreadsheet4' with corresponding ids 'id1', 'id2', 'id3'. 'id4'.

In Spreadsheet1, in the range A1:B of Sheet1, there is a list of usernames corresponding to a spreadsheet id as follows:

Username ID
Player1 "id2"
Player2 "id3"
Player3 "id4"

In Apps Script I am developing a dialog box which contains - among other things - a drop-down menu and a button. The items of the drop-down menu are derived from a two-column range from Spreadsheet1. Let's assume that the range of data is the following:

Warehouse Username
Warehouse1 Player1
Warehouse2 Player2
Warehouse3 Player3

This range is of course loaded as an array of arrays in JavaScript: [[Warehouse1, Player1],[Warehouse2, Player2], [Warehouse3, Player3].

For each item in the drop-down menu there are two attributes: option.text and option.value. The first corresponds to the data of the warehouse column and the other to the data of the username column.

Spreadsheet2, Spreadsheet3 and Spreadsheet4 belong to Player1, Player2 and Player3 respectively. So, when someone clicks the button I want it to trigger a function doing the following:

  1. Get the option.value from the selected option from the drop-down list (i.e. the username corresponding to the selected warehouse)
  2. Get the A1:B range from Spreadsheet1's Sheet1 (the first table shown in this post)
  3. Find the spreadsheet id that corresponds to the selected username in order to append some data in that spreadsheet.

I tried to do this as follows:

First, I wrote the following function in Code.gs to get the range shown in the first table of this post:

function getUserIds() {
  var ss = SpreadsheetApp.openById("id1");
  var meliSheet = ss.getSheetByName("ΜΕΛΗ");
  var getLastRow = meliSheet.getLastRow();
  return meliSheet.getRange(2, 1, getLastRow -1, 2).getValues();
}

In Code.gs I also wrote a function to append data in spreadsheets:

function appendData(val, x, y) {
  var ss = SpreadsheetApp.openById(x);
  var synallagesSheet = ss.getSheetByName(y);
  return synallagesSheet.appendRow(val);
}

Then, in the HTML file of the dialog box I wrote the following code:

<script>
function validationBtn(){
  google.script.run.withSuccessHandler(function(ar)
  {
    var apothikes2 = document.getElementById("apothikes2");
    //apothikes2 is the id of the drop-down list
    var username = apothikes2.options[apothikes2.selectedIndex].value;
    for (let i=0; i<=ar.length; i++ ){
      if (ar[i][0] = username){
        google.script.run.appendData([value1, value2, value3], ar, "ΣΥΝΑΛΛΑΓΕΣ");
      };
    };
  }).getUserIds();
};
</script>

When I click the button I get an "Uncaught at appendData" error at the following line:

var ss = SpreadsheetApp.openById(x);

Implying that the script cannot recognise what I put as an imput for the spreadsheet id. I tried to solve this issue by modifying the code in validationBtn so as to first convert to string the input for the spreadsheet id:

function validationBtn(){
  google.script.run.withSuccessHandler(function(ar)
  {
    var apothikes2 = document.getElementById("apothikes2");
    var username = apothikes2.options[apothikes2.selectedIndex].value;
    for (let i=0; i<=ar.length; i++ ){
      if (ar[i][0] = username){
        userIds = JSON.stringify(ar);
        x = userIds[i][1];
        google.script.run.appendData([value1, value2, value3], x, "ΣΥΝΑΛΛΑΓΕΣ");
      };
    };
  }).getUserIds();
};

But I get exactly the same error when I click the button. I also get the following error: "Uncaught TypeError: Cannot set properties of undefined (setting '0')"

How to fix this? Please also point out any other issue my code may have.

UPDATE: It seems the problem is more general: any kind of data I import into the script using .getValues() - like for example in my appendData function above - is recognised as 'undefined'. I tested this with various functions that were coded to import data from google spreadsheets using .getValues() or .getValue(), and console.log always returned 'undefined' for each of these functions, but when I tried to change the content of a HTML element, it worked. For example, let's say I have a div element with the id "demo" and let's say my imported data are represented by the array 'ar' as above, and let's say that the value of ar[0][0] is 'Player1'. Then if I write the following:

document.getElementById("demo").innerHTML = ar[0][0]

the div content indeed becomes 'Player1' when I run the script. But this is the only thing that works, in any other case the script characterises ar (and any other array I got with .getValues()) as 'undefined'. This is why when I try to input an element of ar as a variable in the appendData function I get an 'Uncaught TypeError: Cannot set properties of undefined'. If this the case then, what could I do to solve this issue?

0 Answers
Related