lundi 12 octobre 2020

How to run an efficient loop in google apps script over a button?

first of all, I am not a developer, so sorry if my code is not beautiful.

So i currently have a list with multible drop down menus and an empty table below it. I created a button and by pressing it, i would like the values from the drop down Menus, copied in correct cells of the table.

I created an if else statement, which works perfectly fine for the first to entries, but if i press the button a third time, the loop repeats and overwrites the second column. I tried it with a for while loop and a switch, which did not work out, since im lacking a bit of syntax as it seems.

here is my code:

function myResourceButton() {  
 
  var sheet1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('STAFFING');  
 



  
  if (sheet1.getRange('D35').getValue() != "")
  {
 sheet1.getRange('C12').copyTo(sheet1.getRange('C36'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C14').copyTo(sheet1.getRange('D36'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C18').copyTo(sheet1.getRange('E36'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C20').copyTo(sheet1.getRange('F36'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C22').copyTo(sheet1.getRange('G36'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C26').copyTo(sheet1.getRange('H36'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C28').copyTo(sheet1.getRange('I36'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 
 }
 else if (sheet1.getRange('D37').getValue() != "")
 
 {
 

 
  }else{
  
 sheet1.getRange('C12').copyTo(sheet1.getRange('C35'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C14').copyTo(sheet1.getRange('D35'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C18').copyTo(sheet1.getRange('E35'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C20').copyTo(sheet1.getRange('F35'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C22').copyTo(sheet1.getRange('G35'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C26').copyTo(sheet1.getRange('H35'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 sheet1.getRange('C28').copyTo(sheet1.getRange('I35'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
 
  }


 sheet1.getRange('C12').clearContent()
 sheet1.getRange('C14').clearContent()
 sheet1.getRange('C18').clearContent()
 sheet1.getRange('C20').clearContent()
 sheet1.getRange('C22').clearContent()
 sheet1.getRange('C26').clearContent()
 sheet1.getRange('C28').clearContent()
 
}

Thank you in advance!

Aucun commentaire:

Enregistrer un commentaire