Search code examples
google-apps-scriptgoogle-apps-script-addon

Build object from another object or array in Google Apps Script and javascript/jQuery


I am receiving the following object with the function in my Spreadsheet Add-on with Google Apps Script:

Function:

function collectAllData(){
  var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(DATA);
  var fileData = sheet.getDataRange().getValues();
  Logger.log(fileData);
  return fileData;
}

Object Received:

[[File ID 1, File Name 1, File Type 1, File Category 1, File Image 1, File Description 1], [File ID 2, File Name 2, File Type 2, File Category 2, File Image 2, File Description 2], ...]

I need to convert this object to the following object in my HTML template

 <script>
    try {
            var file_data = ({
                'File ID 1':{
                    id:"File ID 1",
                    name:"File Name 1",
                    type:"File Type 1"
                    cat:"File Category 1",
                    desc:"File Description 1",
                    img:"File Image 1"
                },
                'File ID 2':{
                    id:"File ID 2",
                    name:"File Name 2",
                    type:"File Type 2"
                    cat:"File Category 2",
                    desc:"File Description 2",
                    img:"File Image 2"
                }
            });
        } catch(e) {
            var file_data = null;
        }
    </script>

Is there any proper way of doing this in my Code.gs and then passing it to the template.html?

I am currently building this in my template.html, but I am sure that there are better ways to do this. Additionally, I constantly get the following error: SyntaxError: missing } after property list

here is my current code from my template.html

  <script>
  try {
  var file_data = ({
  <? for (var i=1; i<fileData.length; i++){
  var x = i < fileData.length-1 ? ',' : ''; ?>
    '<?!= fileData[i][8] ?>':{
       id:"<?!= fileData[i][8] ?>",
       title:"<?!= fileData[i][0] ?>",
       ext:"<?!= fileData[i][1] ?>",
       cat:"<?!= fileData[i][2] ?>",
       cost:"<?!= fileData[i][3] ?>",
       desc:'<?!= fileData[i][4] ?>',
       img:"<?!= fileData[i][5] ?>",
       url:"<?!= fileData[i][6] ?>",
       status:"<?!= fileData[i][7] ?>"
  }<?!= x;
  }?>
  });
  } catch(err) {
    var file_data = null;
  }
  </script>

Solution

  • You can easily stringify the data and send it to client1 and parse it client side.

    Snippet:

    Server side:

    function collectAllData(){
    .
    .
      return JSON.stringify(fileData); 
    }
    

    ClientSide:

    <script>
    var file_data={};
    google.script.run
        .withSuccessHandler(parseArray)
        .collectAllData();
    
    function parseArray(arr){
    arr = JSON.parse(arr);
    arr.forEach((row)=>{
      file_data[row[0]] ={
        id:row[0],
        name:row[1],
        type:row[2],
        cat:row[3],
        desc:row[5],
        img:row[4]
      }
    })
    console.log(file_data);
    }
    </script>
    

    You can also do the parsing server side and pass the stringified parsed object.