CODEWITHSUNDEEP
javascriptgoogle-apps-scriptweb-applicationsgoogle-sheets
some_name.py
some_name.py

Reputation: 827

Pass variable between two functions

I want to write a app which generates a spreadsheet in my google-drive with the user input data with google-app scripts.

For doing so I have several JavaScript-functions I want to execute one after the other:

function createSpreadsheet(name){

  var ss=SpreadsheetApp.create(name);
  var ssID = ss.getID()

}

function writeData(data){

  var s = ss.getSheets()[0];
  s.getRange('A1').setValue(data);

}

which are called by the frond-end script via:

 <script>

  function parse_to_backend(){

      var name = document.getElementById("user_name").value;
      var data = document.getElementById("user_data").value;

      google.script.run.createSpreadsheet(name);
      google.script.run.writeData(data);

  };

  </script>

how can I achieve that the writeData knows ssID the ID of the spreadsheet (I can not return values to the frontend with google.script.run and parse it as an argument)?

Upvotes: 3

Views: 7127

Answers (1)

Tanaike
Tanaike

Reputation: 201358

  • You want to create new Spreadsheet from HTML side.
  • You want to put the value to the created Spreadsheet from HTML side.
  • In your situation, you don't want to put writeData() in createSpreadsheet(). Namely, you want to individually use both functions.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

At first, the modification point is as follows.

Modification point:

When you run the script as follows,

google.script.run.createSpreadsheet(name);
google.script.run.writeData(data);

Function of writeData() is run before the function of createSpreadsheet() is finished, because google.script.run works by the asynchronous process. In order to avoid this, in this sample script, withSuccessHandler() is used.

About the modified script, I introduce 2 patterns. Please select one of them.

Pattern 1:

In this pattern, file ID is returned from createSpreadsheet() and the value is put to the created Spreadsheet using the file ID.

Code.gs: Google Apps Script

function createSpreadsheet(name){
  var ss = SpreadsheetApp.create(name);
  var ssID = ss.getId();
  return ssID;
}

function writeData(id, data){
  var ss = SpreadsheetApp.openById(id);
  var s = ss.getSheets()[0];
  s.getRange('A1').setValue(data);
}

index.html: HTML and Javascript

<script>
  function parse_to_backend(){
    var name = document.getElementById("user_name").value;
    var data = document.getElementById("user_data").value;
    google.script.run.withSuccessHandler((id) => {
      google.script.run.writeData(id, data);
    }).createSpreadsheet(name);
  };
</script>

Pattern 2:

In this pattern, file ID is saved by PropertiesService and the value is put to the created Spreadsheet using the file ID got from PropertiesService. In this case, the file ID is saved to PropertiesService. So the created Spreadsheet can be also used by other action of Javascript using the file ID got from PropertiesService.

Code.gs: Google Apps Script

function createSpreadsheet(name){
  var ss = SpreadsheetApp.create(name);
  var ssID = ss.getId();
  PropertiesService.getScriptProperties().setProperty("ssID", ssID);
}

function writeData(data){
  var id = PropertiesService.getScriptProperties().getProperty("ssID");
  var ss = SpreadsheetApp.openById(id);
  var s = ss.getSheets()[0];
  s.getRange('A1').setValue(data);
}

index.html: HTML and Javascript

<script>
  function parse_to_backend(){
    var name = document.getElementById("user_name").value;
    var data = document.getElementById("user_data").value;
    google.script.run.withSuccessHandler(() => {
      google.script.run.writeData(data);
    }).createSpreadsheet(name);
  };
</script>

References:

If I misunderstood your question and this answer was not what you want, I apologize.

Upvotes: 6

Related Questions