how to get value from array to display as text in input field

I am using google sheet as may data and AppScript as my back end, I try my solution but unfortunately none of them work what I’m trying to do is fetch the data from Google Sheets and then display it in input fields

This is the source code for getting data from Google Sheets

        var ss1 = "googleSheetId"
    function getData(id) {
        if (id == undefined)
        id = 14;
        var secondSS = SpreadsheetApp.openById(ss1);
        var secondSh = secondSS.getSheetByName('data');
        var Data = secondSh.getDataRange().getValues();
        Data.splice(0, 1);
        var tmpData = [];
        for (var x = 0; x < Data.length; x++) {
            if (Data[x][12] == id) {
                tmpData.push(Data[x]);
            }
        }
        var finalData = [];
        for (var x = 0; x < tmpData.length; x++) {
            for (var y = 4; y < tmpData[x].length; y++) {
                if (tmpData[x][y] != '') {
                    finalData[y - 3] = tmpData[x][y];
                }
            }
        }
        return { finalData }
    }

This is the source code for getting data from getData function After fetching the data, it will look like this

{finalData=[null, data1, Mon Mar 02 00:00:00 GMT+03:00 2020, [e-mail], null, data2, Mon Mar 02 00:00:00 GMT+03:00 2020, data3, data4, 14.0, null, yes, yes, null, null, null, Sun Aug 30 12:18:00 GMT+03:00 2020]}

What I’m trying to do is fetch the data where it is and display it in the input fields The application that I am working on obtains the order number from the link of the script and then passes it in the function, which in turn will fetch the data and then display and display each information in an input field The user will enter a link that looks like this:

https://script.google.com/a/herfy.com/macros/s/AKfycbwBrVDszfnKpewofnpweonfpwoenfwpeofnwpefn/dev“`?page=report&request=100“`

request=100 will get row number 100 and will pass it to getData then the data will be display in an input field or Or in my case more than one input field

I use google.script.url.getLocation to get data from the link

google.script.url.getLocation(request_data => {
        document.getElementById('request_breadcrumb').innerText = `request no ${request_data.parameter.request}`;
        document.getElementById('request_title').innerText = `request no ${request_data.parameter.request}`;
      })

I use the corporate version and I do not use the built-in app script.

Answer

After many attempts, I managed to find the solution to the problem and decided to answer it to help others

Google App Script Code

var ss1 = "googleSheetId";
function getReportData(id){
  if(id == undefined)
    id = 14;
  var secondSS = SpreadsheetApp.openById(ss1);
  var secondSh = secondSS.getSheetByName('data');
  var Data = secondSh.getDataRange().getValues();
  var tmpData = [];
  for(var x=0 ; x<Data.length ; x++){
    if(Data[x][12] == storeId){
      tmpData.push(Data[x]);
    }
  }
  
  var finalData = [];
  for(var x=0 ; x<tmpData.length ; x++){
    for(var y=0 ; y<tmpData[x].length ; y++){
      if(tmpData[x][y] != ''){
//Here I add String the `String` object is used to 
//represent and manipulate a sequence of characters.
       finalData[y] = String(tmpData[x][y]);
       }
    }
  }
//Here I removed the parentheses
  return finalData;
}

This is the source code for getting data from getData function After fetching the data, it will look like this and I am using console.log(finalData); as you can see if data is null It will have a place but will not be written null

[, 'data1', 'Mon Mar 02 00:00:00 GMT+03:00 2020', '[e-mail]', , 'data2', 'Mon Mar 02 00:00:00 GMT+03:00 2020', 'data3', 'data4', '14', , 'yes', 'yes', , , , 'Sun Aug 30 12:18:00 GMT+03:00 2020']

Now if use the same link concept https://script.google.com/a/herfy.com/macros/s/AKfycbwBrVDszfnKpewofnpweonfpwoenfwpeofnwpefn/dev ?page=report&request=100

with this code if the page is Loaded it will get request=100

document.addEventListener("DOMContentLoaded",function(){
    google.script.url.getLocation(request_data => {
      var id = request_data.parameter.request;
      google.script.run.withSuccessHandler(showReport).withFailureHandler(showError2).getReportData(storeId);
    })
});

showReport function

    function showReport(data) {
    /**
    * If data in column number 13 which is equal to array 12 is undefined or empty or null
    * Display alert else complete the sequence
    */
    if (data.at(12) == undefined || data.at(12) == " " || data.at(12) == null) {
        alertUser("danger", "No data try again later!")
        document.getElementById('disabled_form').style.display = "none";
        document.getElementById('enabled_form').style.display = "none";
    } else {
        /**
         * If data in column number 14 which is equal to array 13 is equal 'yes' or
         * data in column number 16 which is equal to array 15 is equal 'yes' show
         * the first model which is completely disabled else complete the sequence
         */
        if (data.at(13) == "yes" || data.at(15) == "yes") {
            document.getElementById('enabled_form').style.display = "none";
            document.getElementById('request_breadcrumb').innerText = `Request No ${data.at(12)}`;
            document.getElementById('date_and_time_of_report').value = data.at(0);
            document.getElementById('submitted_name_by').value = data.at(1);
            document.getElementById('mobile_number').value = data.at(2);
            document.getElementById('branch_number').value = data.at(3);
            document.getElementById('reporter_email').value = data.at(6);
            document.getElementById('preferred_language').value = data.at(7);
            document.getElementById('request_type').value = data.at(8);
            document.getElementById('expiration_date').value = data.at(9);
            document.getElementById('report_status').value = data.at(10);
            document.getElementById('email_was_sent_for_report_cancelled').value = data.at(13);
            document.getElementById('email_was_sent_stating_request_is_being_processed').value = data.at(14);
            document.getElementById('email_was_sent_the_request_has_been_executed_successfully').value = data.at(15);
            document.getElementById('note_on_request').value = data.at(16);
            document.getElementById('last_update_of_the_report').value = data.at(19);
            document.getElementById('email_of_editor').value = data.at(20);
            /**
             * display enabled model
             */
        } else {
            document.getElementById('disabled_form').style.display = "none";
            document.getElementById('request_breadcrumb').innerText = `Request No ${data.at(12)}`;
            document.getElementById('date_and_time_of_report_enabled_form').value = data.at(0);
            document.getElementById('submitted_name_by_enabled_form').value = data.at(1);
            document.getElementById('mobile_number_enabled_form').value = data.at(2);
            document.getElementById('branch_number_enabled_form').value = data.at(3);
            document.getElementById('reporter_email_enabled_form').value = data.at(6);
            document.getElementById('preferred_language_enabled_form').value = data.at(7);
            document.getElementById('request_type_enabled_form').value = data.at(8);
            document.getElementById('expiration_date_enabled_form').value = data.at(9);
            document.getElementById('report_status_enabled_form').value = data.at(10);
            document.getElementById('email_was_sent_stating_request_is_being_processed_enabled_form').value = data.at(14);
            document.getElementById('last_update_of_the_report_enabled_form').value = data.at(19);
        }
    }
}

showError function

function showError(error){
    alert(error)
}

I use the corporate version and I do not use the built-in app script.

Live Demo

Reference

• Introduction to the DOM
• Class google.script.url (Client-side API)