HTML list from data stored in Google Spreadsheet for Apps Script website/webapp - Templated HTML

Question

My Google Apps Script web app I'm trying to make has a button that the user clicks and adds their name to. I'm trying to have that information save as a list on the website so that if I refresh the page it's still there and if someone else is accessing the website, it will still be there.

This is the add button - https://snag.gy/EOgx5T.jpg
These are example names added from the button- https://snag.gy/8GtX3Y.jpg

I don't want to save it as local storage. Is there a way to save the information to a Google Spreadsheet and use that spreadsheet as a database or something?

index.html

<div id="artistTabs">
   <ul id="artistList">
     <li><a onClick="addArtist(); return false;" href="">+</a></li>
  </ul>
</div>

js.html

function addArtist()
{
   console.log("add artist here");
   var artistName = prompt("enter artist whole first name and initial of last name");
    if (artistName === "") //user pressed 'ok' but input field was empty
    {
        return;
    }
    else if (artistName != "" && artistName !== null) //user inputs something and hits 'ok'
    {
         var ul = document.getElementById("artistList");
         var li = document.createElement("li");
         var a = document.createElement("a");
         a.setAttribute("href", "#");
         a.textContent = artistName;
         li.appendChild(a);
         ul.appendChild(li);   
         li.onclick = function()
         {
            test(); return false;
         };
   }
   else //user hits 'cancel' or 'x' 
   {
        return;
   }
}

function test()
{
    console.log("test");
}

Do I need to add something to the Google Apps Script 'xxx.gs' or 'xxx.js' file to save the information/access a database/something?

Edit

I've updated some of the code. I can write the artist to the spreadsheet and i can get the artist name back from the spreadsheet. I'm still confused on how to display a new item in the list as templated html.

index.html

<div id="artistTabs">
    <ul id="artistList">
        <li><a onClick="addArtist(); return false;" href="">+</a></li>
    </ul>
</div>

js.html

function addArtist()
{
  var artistName = prompt("enter artist whole first name and initial of last name");

  if (artistName === "") //user pressed 'ok' but input field was empty
  {
    return;
  }
  else if (artistName != "" && artistName !== null) //user inputs something and hits 'ok'
  {
    google.script.run
      .withSuccessHandler(writeSuccess(artistName))
      .withFailureHandler(writeFailure)
      .writeArtistName(artistName);
  }
  else //user hits 'cancel' or 'x'
  {
    return;
  }
}

function writeSuccess(artistName)
{
  console.log("write success: " + artistName);
}

function writeFailure()
{
  console.log("write failure - email myself why it failed and the time it failed");
} 

code.gs

var ss = SpreadsheetApp.openById('id');
var sheet = ss.getSheets()[0];

function doGet()
{
  return HtmlService.createTemplateFromFile('index').evaluate();
}

function getContent(filename)
{
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function writeArtistName(artistName)
{
  var lastRow = sheet.getLastRow() + 1; //gets next unused row
  var column = 1; //first column
  var lastCell = sheet.getRange(lastRow, column);

  lastCell.setValue(artistName); //sets next blank row as artistName
}

function getArtistName()
{
  var lastRow = sheet.getLastRow(); //gets last row with content
  var column = 1;

  var lastArtist = sheet.getRange(lastRow, column).getValue(); //gets last row with artistName

  Logger.log(lastArtist);

  var x = '<li><a onClick="test(); return false;" href="#">';
  var y = lastArtist;
  var z = '</a></li>';

  var xyz = x + y + z;

  Logger.log(xyz);
}

How do I call the getArtistName() function in the code.gs file, set the artist name as a list item in my unordered list, and display it on the website as an html template? i cant call document.getElementById in the .gs file because it says ReferenceError: "document" is not defined.

i need the list to stay on the website even if i reload the page and it needs to be accessible to anyone from anywhere

Answer 1

You will need to build HTML on the server. So change your HTML to this:

<div id="artistTabs">
   <ul id="artistList">
      <?!= createTheList(); ?>
  </ul>
</div>

Server Code

function createTheList() {
  var artists,howManyRows,howManyColumns,i,L,newList,ss,sh,startingRow,startingColumn,
    thisArtist,templateHTML,thisRow;

  ss = SpreadsheetApp.openById('file ID goes here'); //Get spreadsheet
  sh = ss.getSheetByName('sheet tab name');

  startingRow = 2;
  startingColumn = 1;
  howManyRows = sh.getLastRow() - 1;
  howManyColumns = 1;

  artists = sh.getRange(startingRow,startingColumn,howManyRows,howManyColumns)
    .getValues();//Get all artist names
  L = artists.length; //How many artist in the list

  artists = artists.toString().split(",");//Flatten 2D array to 1D

  //template of blank row of HTML with a merge string of "z_z_z_z_z"
  templateHTML = "<li><a onClick="addArtist(); return false;" href="">z_z_z_z_z</a></li>";

  newList = "";//Initialize

  for (i=0;i<L;i+=1) {
    thisRow = "";//reset on every loop
    thisArtist = artists[i];

    thisRow = templateHTML.replace("z_z_z_z_z",thisArtist);//Inject artist name
    newList = newList + thisRow;//Keep adding to the list
  };

  return newList;
};

You can send the data to a Google Spreadsheet. In your case, the value collected has a variable name of artistName. So you can call an Apps Script function in a .gs file like this:

GS File:

function writeToSpreadsheet(data) {
  var ss, sh;

  //Save data to a spreadsheet
  ss = SpreadsheetApp.openById('file ID goes here'); //Get spreadsheet
  sh = ss.getSheetByName('sheet tab name');
  sh.appendRow([data]);//Append data to end of sheet tab data
};

Client Side Code:

<script>
  function someFunctionName(artistName) {//Either pass data to function
    //Or get the data
    var artistName = code to get the value;

    google.script.run
      .withSuccessHandler(serverCodeCompleted)
     .writeToSpreadsheet(artistName);
  };

  function serverCodeCompleted() {
    //Show message to the user
    some code here
  };
</script>

To get the data to display in your page, you should use templated HTML.

Apps Script documentation