How to pass variable from javascript to google sheet web app

Question

So I have 5 questions with dropdowns. Dropdown has 1-5 options. I stored their selections for each question as an array but I'm not sure how to save them in my Google sheets. I'm definitely new in creating google web apps. Any help is highly appreciated.

html

  <p>1. Question no 1. Rate from 1 -5</p>       
            </div>
            <div class="col-2">
                <select id="1_kscore" class="form-control kscore" name="Value[]" style="font-size:8pt">
                  <option selected>NA</option>
                  <? for(var i = 0; i < dd_score.length; i++) { ?>      
                <option value="<?= dd_score[i] ?>" ><?= dd_score[i] ?></option>
              <? } ?>
            </select>
            </div>
          </div> <!--- close row -->

          <div class="row" style="margin-bottom:8px">
            <div class="col-10">
              <p>2. Question no 2. Rate from 1 -5   </p>        
            </div>
            <div class="col-2">
                <select id="2_kscore" class="form-control kscore" name="Value[]" style="font-size:8pt">
                  <option selected>NA</option>
                  <? for(var i = 0; i < dd_score.length; i++) { ?>      
                <option value="<?= dd_score[i] ?>" ><?= dd_score[i] ?></option>
              <? } ?>
            </select>
            </div>
          </div> <!--- close row -->

and so on...

code.gs

function writeArray(c_score){

var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var lR = sh1.getLastRow() + 1;
sh1.getRange(lR,1,1,5).setValues([c_score]);

}

javascript

function submit(){

  c_selects = document.getElementsByClassName("cscore");
  for(var i=0;i<c_selects.length;i++){
    c_score = c_selects[i].value;
    alert(c_score);
    google.script.run.writeArray(c_score);  

  }  
}

variable c_score are the scores for each 5 questions I have w/c for example are 5,5,4,5,5

Aren't your options always NA and the numbers 1 to 5? And why is writeArray in a loop? You have no way of knowing in which order they are executed in sheet. google.script.run is an asynchronous process. — TheWizEd
– TheWizEd, Commented Jul 11, 2022 at 19:40

TheWizEd · Accepted Answer · 2022-07-20 17:29:17Z

1

Description

I've put together a sample dialog and script that will get the scores and place them in a spreadsheet.

HTML_Score.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div class="row" style="margin-bottom:8px">
      <div class="col-10">
        <p>1. Question no 1. Rate from 1 -5</p>       
      </div>
      <div class="col-2">
        <select id="1_kscore" class="form-control kscore" name="Value[]" style="font-size:8pt">
          <option selected>NA</option>
          <? for(var i = 0; i < dd_score.length; i++) { ?>      
            <option value="<?= dd_score[i] ?>" ><?= dd_score[i] ?></option>
          <? } ?>
        </select>
      </div>
    </div> <!--- close row -->

    <div class="row" style="margin-bottom:8px">
      <div class="col-10">
        <p>2. Question no 2. Rate from 1 -5   </p>        
      </div>
      <div class="col-2">
        <select id="2_kscore" class="form-control kscore" name="Value[]" style="font-size:8pt">
          <option selected>NA</option>
          <? for(var i = 0; i < dd_score.length; i++) { ?>      
            <option value="<?= dd_score[i] ?>" ><?= dd_score[i] ?></option>
          <? } ?>
        </select>
      </div>
    </div> <!--- close row -->

    <div class="row" style="margin-bottom:8px">
      <div class="col-10">
        <p>2. Question no 3. Rate from 1 -5   </p>        
      </div>
      <div class="col-2">
        <select id="2_kscore" class="form-control kscore" name="Value[]" style="font-size:8pt">
          <option selected>NA</option>
          <? for(var i = 0; i < dd_score.length; i++) { ?>      
            <option value="<?= dd_score[i] ?>" ><?= dd_score[i] ?></option>
          <? } ?>
        </select>
      </div>
    </div> <!--- close row -->

    <div class="row" style="margin-bottom:8px">
      <div class="col-10">
        <p>2. Question no 4. Rate from 1 -5   </p>        
      </div>
      <div class="col-2">
        <select id="2_kscore" class="form-control kscore" name="Value[]" style="font-size:8pt">
          <option selected>NA</option>
          <? for(var i = 0; i < dd_score.length; i++) { ?>      
            <option value="<?= dd_score[i] ?>" ><?= dd_score[i] ?></option>
          <? } ?>
        </select>
      </div>
    </div> <!--- close row -->

    <div class="row" style="margin-bottom:8px">
      <div class="col-10">
        <p>2. Question no 5. Rate from 1 -5   </p>        
      </div>
      <div class="col-2">
        <select id="2_kscore" class="form-control kscore" name="Value[]" style="font-size:8pt">
          <option selected>NA</option>
          <? for(var i = 0; i < dd_score.length; i++) { ?>      
            <option value="<?= dd_score[i] ?>" ><?= dd_score[i] ?></option>
          <? } ?>
        </select>
      </div>
    </div> <!--- close row -->    

    <div>
      <input type="button" value="Submit" onclick="submit()">
    </div>   
    <?!= include('JS_Score'); ?>    
  </body>
</html>

JS_Score.html

<script>
  function submit() {
    try {
      let scores = document.getElementsByClassName("kscore");
      let values = [];
      for( let i=0; i<scores.length; i++ ) {
        values.push([scores[i].value]);
      }
      google.script.run.writeArray(values);
    }
    catch(err) {
      alert(err);
    }
  }
</script>

Code.gs

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

function showTest() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
  let dd_score = sheet.getRange(1,1,5,1).getValues().flat();
  var html = HtmlService.createTemplateFromFile("HTML_Score");
  html.dd_score = dd_score;
  SpreadsheetApp.getUi().showModalDialog(html.evaluate(),"Test");
}

function writeArray(scores) {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
    sheet.getRange(1,3,5,1).setValues(scores);  // Put in column C
  }
  catch(err) {
    Logger.log(err);
  }
}

Dialog

Modified scripts for comments below:

<script>
  function submit() {
    try {
      let scores = document.getElementsByClassName("kscore");
      let values = [];
      for( let i=0; i<scores.length; i++ ) {
        values.push(scores[i].value);  // <- create a row
      }
      google.script.run.writeArray(values);
    }
    catch(err) {
      alert(err);
    }
  }
</script>

function writeArray(scores) {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
    sheet.getRange(sheet.getLastRow()+1,1,1,5).setValues([scores]);  // Put in range An:En (n = new row)
  }
  catch(err) {
    Logger.log(err);
  }
}

edited Jul 20, 2022 at 17:29

answered Jul 11, 2022 at 20:08

TheWizEd

8,6542 gold badges14 silver badges23 bronze badges

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

7 Comments

Emily George Over a year ago

thank you so much TheWizEd it's working. but i am struggling to write the values in a way that the next score will be recorded in the next column rather than the next row

TheWizEd Over a year ago

What do you mean by next row? I don't understand your question. Right now the score is placed in column C. Or do you mean if someone else answers the questions the results should go to column D, then E, etc?

Emily George Over a year ago

yes that is. so instead of the scores being written in C1:C6, it will be written in A2:E2

TheWizEd Over a year ago

Then A3:E3, then A4:E4, etc?

Emily George Over a year ago

that is correct

|

Collectives™ on Stack Overflow

How to pass variable from javascript to google sheet web app

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related