Need to make simple read and update webapp using appscript

Question

I have a google sheet like this

I Need to make a google appscript webapp like below with the above sheet.

only the price field need to be editable. When it is edited and save is clicked, it should be updated in the sheet.

How i can do this in a easy way?

PS:I know html and css part well. I just need the script part.

Thanks in Advance

Answer 1

A script to get your data

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet Name");
  const [h,...vs] = sh.getDataRange().getValues();
  Logger.log(JSON.stringify(vs));//view 2d array that stores your data
  return vs
}

You can call it in a window.onload using google.script run or use templated html to load the data server side prior to rendering.

There are many examples of this on SO.

Answer 2

I think the easiest approach is using Templated HTML. It allows you to easily use your Google Apps Script functions inside your HTML, like ejs or Jinja2.

Code.gs

/* Sheet where the data lives */
const sS = SpreadsheetApp.openById('<SS_ID>').getSheetByName('StoreData')
/* Function to extract the data */
const getStoreData = () =>  sS.getDataRange().getValues().slice(1)
/* HTTP/GET function (For using template HTML you need to evaluate your file) */
const doGet = () => HtmlService.createTemplateFromFile('Index').evaluate()

And from using the getStoreData inside the HTML use the <? ?>, syntax.

Index.html

<div class="store-container">
<? getStoreData().forEach((item) => { ?>
  <div>
    <img src="<?= item[1] ?>">
    <span>Price: <?= item[2] ?></span>
    <span>Id: <?= item[0] ?> </span>
  </div>
<? }) ?>
</div>

After this, you only need to style it as you need.

Documentation:

• SpreadsheetApp
• openById
• getDataRange()
• getValues()

Answer 3

I believe your goal is as follows.

• You want to retrieve the values from the Spreadsheet and create the situation you showing with Web Apps.
• When the value of the price is changed on Web Apps and click the save button, you want to update the column "C" of the Spreadsheet.

In this case, how about the following sample script?

Usage:

1. Sample script:

Google Apps Script side: Code.gs

Please copy and paste the following script to the script editor of Google Spreadsheet as a script. And, please set the sheet name.

const sheetName = "Sheet1"; // Please set the sheet name.

function doGet() {
  const split = 4; // When this value is changed, the number of columns can be changed.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const values = sheet.getRange("A2:C" + sheet.getLastRow()).getValues();
  let res = [];
  do {
    const temp = values.splice(0, split).reduce((s, [a, b, c]) =>
      s += `<td><img src="${b}"><br>${a}<br><input type="number" name="saveValue" value="${c}"><input type="button" value="save" onclick="save();"></td>`
      , "");
    res.push(`<tr>${temp}</tr>`);
  } while (values.length > 0);
  const html = HtmlService.createTemplateFromFile("index");
  html.table = res.join("");
  return html.evaluate();
}

function saveValues(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getRange(2, 3, e.length, 1).setValues(e);
}

HTML & Javascript side: index.html

Please copy and paste the following script to the script editor of Google Spreadsheet as a HTML.

<table><?!= table ?></table>
<script>
function save() {
  const obj = document.getElementsByName("saveValue");
  const values = [...obj].map(e => [e.value]);
  google.script.run.saveValues(values);
}
</script>

2. Deploy Web Apps.

The detailed information can be seen at the official document. In this case, it supposes that you are using new IDE. Please be careful this.

1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
2. Please click "Select type" -> "Web App".
3. Please input the information about the Web App in the fields under "Deployment configuration".
4. Please select "Me" for "Execute as".
5. Please select "Anyone" for "Who has access".
   • For testing this script, I thought that this setting might be suitable.
6. Please click "Deploy" button.
7. Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.
   • When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
   • You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

3. Testing.

Please access to the Web Apps using the browser. By this, the following result is obtained.

Sample Spreadsheet:

Sample Web Apps:

• For example, when you change the price from 100 to 101 and click the save button, the column "C" of Spreadsheet is updated.

Note:

• This is a simple sample script for answering your question. So, please modify this for your actual situation.

References:

• Web Apps
• HtmlFormApp
• Taking advantage of Web Apps with Google Apps Script