3

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)?

1 Answer 1

6
  • 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.

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

2 Comments

The perfect answer! Yes I wasn't aware of the run.withSuccessHandler and because I have multiple functions to deploy I didn't want to put them all together. Thanks a lot for the help!
@user7784503 Thank you for replying. I'm glad your issue was resolved. Thank you, too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.