CODEWITHSUNDEEP
htmlgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
Boomer
Boomer

Reputation: 73

how to execute functions in the right order G script

already finishing my project but another problem popped up. When i try to run several functions after button click in sidebar some of them are executed faster than the others. Can someone help to fix please? 

<!DOCTYPE html>
    <html>
  <head>
    <base target="_top">

<style>
body {
    background-color: #fafab6; 
    }


</style>

<form name="submit-to-google-sheet">

   <BR>

  <label for="Cinnost">Cinnost: </label>
  <select name="Cinnost" type="text" placeholder="Cinnost">
   <option value="Konstrukce">Konstrukce</option>
   <option value="Montaz">Montaz</option>
   <option value="Management">Uklid</option>
   <option value="Udrzba / uklid">Udrzba / uklid</option>
  </select>

  <BR>
  <BR>
   Zvol cas:

  <input type="Time" name="usr_time" placeholder="Time">

  <BR>
  <BR> 
  <BR>
  <BR>
  <button type="submit">Send</button>

  <input type="reset">
</form>


<script>

 function pokusnak() {

 google.script.run.copykomplet();

 } 

  const scriptURL = 'https://script.google.com/macros/s/AKfycbx0rzbLMsXKQ1C3--qP-wHtcFdfvmOG--y_Qpk0xc43EqtUdSeF/exec'
  const form = document.forms['submit-to-google-sheet']

  form.addEventListener('submit', e => {
    e.preventDefault()
    fetch(scriptURL, { method: 'POST', body: new FormData(form)})
      .then(response => console.log('Success!', response))
      .catch(error => console.error('Error!', error.message))

   //executes before the lines above
   pokusnak();
   // when left here to close the sidebar executes before the lines before and stops the program
   google.script.host.close();
  } 
  )

I need to post to the G sheets 1st then run the function "pokusnak" which triggers google.script.run with function Copykomplet, please find below. and then close the sidebar.

Problem is the functions are not executing in the right order.

Code.gs: 

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()


function onOpen()
{
  SpreadsheetApp.getUi().createMenu('My Tools')
    .addItem('createTextEntryForm', 'createTextEntryForm')
    .addToUi();

  loadSideBar();
  SpreadsheetApp.getUi().createMenu('My Menu').addItem('loadSidebar', 'loadSideBar').addToUi();  
}


function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1

    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }

}

function loadSideBar()
{

 var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getActiveSheet(),
      ui = SpreadsheetApp.getUi(),
      cell = sheet.getCurrentCell(),
      val = cell.getValue(),
      col = cell.getColumn(),
       row = cell.getRow(),
      zacni = sheet.getRange(row, col);

  if (col === 7 && row > 7 && val !== 'Pocet dilu: ') {  


  var userInterface=HtmlService.createHtmlOutputFromFile('index');//sidebar for html and formBar for form
  SpreadsheetApp.getUi().showSidebar(userInterface);

 // doPost ();

  } else ui.alert('Nevybral jsi žádný díl, zkus to znovu'); 

}



function delampokus2() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getActiveSheet(),
      ui = SpreadsheetApp.getUi(),
      cell = sheet.getCurrentCell(),
      val = cell.getValue(),
      col = cell.getColumn(),
       row = cell.getRow(),
      zacni = sheet.getRange(row, col);

  if (col === 7 && row > 7 && val !== 'Pocet dilu: ') {

       var sss4 = SpreadsheetApp.openById('1_yfU1PuBds5KcsqG-iPugpjeYEOX5sdXuFXIuLAIxok');
       var ss4 = sss4.getSheetByName('Otevrene zakazky');
       var radek4 = row
       var source4 = ss4.getRange (radek4,2);
       var destSheet4 = sss4.getSheetByName('Sheet2');
       var destRange4 = destSheet4.getRange(destSheet4.getLastRow(),4);
       source4.copyTo (destRange4, {contentsOnly: true}); 

       var source5 = ss4.getRange (radek4,3);
       var destSheet5 = sss4.getSheetByName('Sheet2');
       var destRange5 = destSheet5.getRange(destSheet5.getLastRow(),5);
       source5.copyTo (destRange5, {contentsOnly: true});        

       var source6 = ss4.getRange (radek4,7);
       var destSheet6 = sss4.getSheetByName('Sheet2');
       var destRange6 = destSheet6.getRange(destSheet6.getLastRow(),6);
       source6.copyTo (destRange6, {contentsOnly: true}); 

       var source7 = ss4.getRange (radek4,9);
       var destSheet7 = sss4.getSheetByName('Sheet2');
       var destRange7 = destSheet7.getRange(destSheet7.getLastRow(),7);
       source7.copyTo (destRange7, {contentsOnly: true}); 

       var destSheet8 = sss4.getSheetByName('Sheet2');
       var destRange8 = destSheet8.getRange(destSheet8.getLastRow(),8);
       destRange8.setValue('Petr')     

    ui.alert('vsetko okej');

  } else ui.alert('Nevybral jsi žádný díl, zkus to znovu');    

}


function moveValuesOnly () {

  var sss = SpreadsheetApp.openById('1_yfU1PuBds5KcsqG-iPugpjeYEOX5sdXuFXIuLAIxok');
  var ss = sss.getSheetByName('Sheet1');

  // ss = SpreadsheetApp.getActiveSpreadsheet ();
  var radek = ss.getLastRow()
  var source = ss.getRange (radek,1);
  var destSheet = sss.getSheetByName('Sheet2');

  // "Sheet1!F1:H3"
  // ss.getRange (radek,1);

  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
  source.copyTo (destRange, {contentsOnly: true});
  //source.clear ();


   var sss2 = SpreadsheetApp.openById('1_yfU1PuBds5KcsqG-iPugpjeYEOX5sdXuFXIuLAIxok');
  var ss2 = sss2.getSheetByName('Sheet1');
    var radek2 = ss2.getLastRow()
  var source2 = ss2.getRange (radek2,2);
  var destSheet2 = sss2.getSheetByName('Sheet2');

  var destRange2 = destSheet2.getRange(destSheet2.getLastRow(),2);
  source2.copyTo (destRange2, {contentsOnly: true});


     var sss3 = SpreadsheetApp.openById('1_yfU1PuBds5KcsqG-iPugpjeYEOX5sdXuFXIuLAIxok');
  var ss3 = sss3.getSheetByName('Sheet1');
    var radek3 = ss3.getLastRow()
  var source3 = ss3.getRange (radek3,3);
  var destSheet3 = sss3.getSheetByName('Sheet2');
   var destRange3 = destSheet3.getRange(destSheet3.getLastRow(),3);
  source3.copyTo (destRange3, {contentsOnly: true});
}


function copykomplet () {

    var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getActiveSheet(),
      ui = SpreadsheetApp.getUi(),
      cell = sheet.getCurrentCell(),
      val = cell.getValue(),
      col = cell.getColumn(),
       row = cell.getRow(),
      zacni = sheet.getRange(row, col);

  var sss = SpreadsheetApp.openById('1_yfU1PuBds5KcsqG-iPugpjeYEOX5sdXuFXIuLAIxok');
  var ss = sss.getSheetByName('Sheet1');
  var radek = ss.getLastRow()
  var source = ss.getRange (radek,1);
  var destSheet = sss.getSheetByName('Sheet2');
  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
  source.copyTo (destRange, {contentsOnly: true});
  //source.clear ();

  var sss2 = SpreadsheetApp.openById('1_yfU1PuBds5KcsqG-iPugpjeYEOX5sdXuFXIuLAIxok');
  var ss2 = sss2.getSheetByName('Sheet1');
  var radek2 = ss2.getLastRow()
  var source2 = ss2.getRange (radek2,2);
  var destSheet2 = sss2.getSheetByName('Sheet2');
  var destRange2 = destSheet2.getRange(destSheet2.getLastRow(),2);
  source2.copyTo (destRange2, {contentsOnly: true});

  var sss3 = SpreadsheetApp.openById('1_yfU1PuBds5KcsqG-iPugpjeYEOX5sdXuFXIuLAIxok');
  var ss3 = sss3.getSheetByName('Sheet1');
  var radek3 = ss3.getLastRow()
  var source3 = ss3.getRange (radek3,3);
  var destSheet3 = sss3.getSheetByName('Sheet2');
  var destRange3 = destSheet3.getRange(destSheet3.getLastRow(),3);
  source3.copyTo (destRange3, {contentsOnly: true});      

       var sss4 = SpreadsheetApp.openById('1_yfU1PuBds5KcsqG-iPugpjeYEOX5sdXuFXIuLAIxok');
       var ss4 = sss4.getSheetByName('Otevrene zakazky');
       var radek4 = row
       var source4 = ss4.getRange (radek4,2);
       var destSheet4 = sss4.getSheetByName('Sheet2');
       var destRange4 = destSheet4.getRange(destSheet4.getLastRow(),4);
       source4.copyTo (destRange4, {contentsOnly: true}); 

       var source5 = ss4.getRange (radek4,3);
       var destSheet5 = sss4.getSheetByName('Sheet2');
       var destRange5 = destSheet5.getRange(destSheet5.getLastRow(),5);
       source5.copyTo (destRange5, {contentsOnly: true});        

       var source6 = ss4.getRange (radek4,7);
       var destSheet6 = sss4.getSheetByName('Sheet2');
       var destRange6 = destSheet6.getRange(destSheet6.getLastRow(),6);
       source6.copyTo (destRange6, {contentsOnly: true}); 

       var source7 = ss4.getRange (radek4,9);
       var destSheet7 = sss4.getSheetByName('Sheet2');
       var destRange7 = destSheet7.getRange(destSheet7.getLastRow(),7);
       source7.copyTo (destRange7, {contentsOnly: true}); 

       var destSheet8 = sss4.getSheetByName('Sheet2');
       var destRange8 = destSheet8.getRange(destSheet8.getLastRow(),8);
       destRange8.setValue('Petr')     


       ui.alert('Zaznam pridan');



}

Upvotes: 0

Views: 883

Answers (1)

Aidan
Aidan

Reputation: 1750

Fetch is asynchronous, as is google.script.run. This means that the call to fetch starts an operation but doesn't wait for it to complete. You are closing your dialog so it never completes. If you want to do something when the operation is finished you have to provide a callback function that will be run on completion.

For fetch() the .then() function is how you provide a callback.

Your postnak() function is calling google.script.run.copyKomplet() which is also asynchronous so you can't call google.script.host.close() until it is finished. The callbacks for google.script.run are provided using withSuccessHandler() and withFailureHandler():

form.addEventListener('submit', e => {
  e.preventDefault()
  fetch(scriptURL, { method: 'POST', body: new FormData(form)})
  .then(response => {
    console.log('Success!', response);
    google.script.run
    .withSuccessHandler(()=>google.script.host.close())
    .withFailureHandler((e)=>alert("Error:" + e))
    .copykomplet();
  })
  .catch(error => console.error('Error!', error.message))
}

As others suggested it would be worth spending some time to understand how this works in JavaScript / Google Apps Script as asynchronous calls are a core part of how these technologies work.

Upvotes: 1

Related Questions