0

I wrote a simple iterator to loop trough a JSON object and parse the object into a new form. This works as expected in almost any JS environment. (For example in the console( However, the function below returns an empty array when executed in AppScript.

returns : [[], [], [], [], []]

This problem seems to be AppScript specific. Before I submit a bug through Google Developer Group I like to understand if this might be an App Script specific and intended behavior.

function parseBQData(/*tableData, request*/) {
  var tableData = [["20220301","(none)","(direct)","3","1","1"],["20220301","organic","google","3","1","1"],["20220302","(none)","(direct)","4","2","2"],["20220302","organic","bing","1","1","1"],["20220303","(none)","(direct)","1","1","1"]]
  try {
    
    // store the array of dimensions and metrics in a variable called 'fields'
    var fields = ["date", "medium", "source", "pageviews", "sessions", "users"]
    
    // create a new empty array to store the parsed data
    var newtableData = new Array();
    
    // loop through each row in the tableData
    for (var i = 0; i < tableData.length; i++) {
      
      Logger.log(tableData[i]) // This returns: [20220301, (none), (direct), 3, 1, 1], [2022]


      // create a new empty array to store the current row
      var wrapper = new Array();
      
      // loop through each column in the row
      for (var j = 0; j < fields.length; j++) {
        wrapper[fields[j]] = tableData[i][j];  /// <-is this not working? 

      Logger.log("Test Log:")
      Logger.log("This is the updated field stored in the wrapper:"+wrapper[fields[j]]) // Returns : "20220301"
      Logger.log("Lets check the wrapper if has the date/first value : " + wrapper.date ) // Returns "20220301"
      // the wrapper does not dissapear but appears as empty when accessed as in root, the assignment abovew worked and the key value pair is accessible
      Logger.log("Wrapper : " + JSON.stringify(wrapper)) // This returns always "Wrapper : []"  Stringify is solely used to see that atleast something is returned and the wrapper is accesible
      Logger.log("This is the current cell: "+fields[j]+" : "+tableData[i][j]) // This returns : "This is the current cell: date : 20220301" ...  This is the current cell: medium : (none) 
      // So in conclusion All values and Arrays are accessible

        // store the column data in the current row array, using the column header as the key
        


      }
      
      // add the current row to the new array of parsed data
      newtableData.push(wrapper);
    }
    
    
    // return the new array of parsed data
    Logger.log(newtableData)  //This returns: "[[], [], [], [], []]""
    return newtableData;
  
  // if there is an error parsing the data, print the error to the log
  } catch (e) {
    Logger.log("Error parsing data")
    Logger.log(e)
  }
}

#Edit: Added some logging and comments

1
  • How are you calling parseBQData? Which line is used to log the return: [[], [], [], [], []]? Commented Jun 29, 2022 at 18:07

3 Answers 3

0

did not try in App Script but works fine with browser JS console :

var TableData = [["20220301","(none)","(direct)","3","1","1"],["20220301","organic","google","3","1","1"],["20220302","(none)","(direct)","4","2","2"],["20220302","organic","bing","1","1","1"],["20220303","(none)","(direct)","1","1","1"]]

function parseBQData(TableData, request) {
  try {
    var fields = ["date", "medium", "source", "pageviews", "sessions", "users"];
    var newTableData = new Array();
    for (var i = 0; i < TableData.length; i++) {
      var wrapper = new Array();
      for (var j = 0; j < fields.length; j++) {
        wrapper[fields[j]] = TableData[i][j];
      console.log("Wrapper : " + wrapper)
      }
      newTableData.push(wrapper);
    }
    return newTableData;
  } catch (e) {
    console.log("Error parsing data")
    console.log(e)
  }
}
parseBQData(TableData, 0);

Logs :

Wrapper : 
(5) [Array(0), Array(0), Array(0), Array(0), Array(0)]
0: [date: '20220301', medium: '(none)', source: '(direct)', pageviews: '3', sessions: '1', …]
1: [date: '20220301', medium: 'organic', source: 'google', pageviews: '3', sessions: '1', …]
2: [date: '20220302', medium: '(none)', source: '(direct)', pageviews: '4', sessions: '2', …]
3: [date: '20220302', medium: 'organic', source: 'bing', pageviews: '1', sessions: '1', …]
4: [date: '20220303', medium: '(none)', source: '(direct)', pageviews: '1', sessions: '1', …]
length: 5

So my best guess, check Logger print args types, and maybe Logger.log("Wrapper : ", wrapper); ?

Sign up to request clarification or add additional context in comments.

2 Comments

How are you executing parseBQData? Script editor?
Exactly, my issue. I dont understand how , why it works this way in AppScript. I have added some comments and Logs to my Code. You can directly copy paste it.
0

In App Script TableData is defined in the global scope. However the parameter TableData passed to parseBQData is in the function scope. If I try to run your script function parseBQData(TableData, request) including TableData in global scope I get TypeError: Cannot read property 'length' of undefined because TableData is undefined. You can fix this by simply:

function parseBQData(request) {

This is not a bug.

I also found this article JavaScript function parameter and scope

4 Comments

thank you I have made sure that the TableData is accessible in the function. See my edited Code above.
Please choose which you think is the best answer. Thanks.
Sorry, my comment might seem misleading, but the issue is still persistent.
wrapper is an array. An Array is an object and is indexed by integer. What you are doing is creating an array property wrapper.date like wrapper.length, not populating the array with a value that can be indexed by date. wrapper[date] is retrieving the property wrapper.date
0

Same output but much simpler

function parseBQData() {
  const iA = [["20220301", "(none)", "(direct)", "3", "1", "1"], ["20220301", "organic", "google", "3", "1", "1"], ["20220302", "(none)", "(direct)", "4", "2", "2"], ["20220302", "organic", "bing", "1", "1", "1"], ["20220303", "(none)", "(direct)", "1", "1", "1"]]
  const fields = ["date", "medium", "source", "pageviews", "sessions", "users"];
  iA.unshift(fields)
  Logger.log(JSON.stringify(iA));
}

Execution log
10:25:24 AM Notice  Execution started
10:25:25 AM Info    [["date","medium","source","pageviews","sessions","users"],["20220301","(none)","(direct)","3","1","1"],["20220301","organic","google","3","1","1"],["20220302","(none)","(direct)","4","2","2"],["20220302","organic","bing","1","1","1"],["20220303","(none)","(direct)","1","1","1"]]
10:25:26 AM Notice  Execution completed

1 Comment

Thank you for showing me a different way to do it, although the JSON structure will end up different as I'm looking to store key value pairs. thank you though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.