Forum Discussion

RyanParker's avatar
RyanParker
Copper Contributor
Oct 20, 2022

Struggling with an office script

I've got a list of ingredients that the user can input onto a table. I'd like them to be able to save this list to create a recipe by clicking a script button. This would then transpose the data, copy and paste as values, then clear the original data. the problem is I'm not sure how to repeat it. The script should ideally go to the next empty row (or at least 4 rows as the data will be 4 rows). Can anyone help?

 

The script recorded was this. I am not good at java at all so I'm not sure how to get it to find the next empty row:

 

function main(workbook: ExcelScript.Workbook) {
  let recipeList = workbook.getWorksheet("RecipeList");
  let selectedSheet = workbook.getActiveWorksheet();
  // Paste to range A11 on recipeList from range L2:L4 on selectedSheet
  recipeList.getRange("A1").copyFrom(selectedSheet.getRange("L2:L4"), ExcelScript.RangeCopyType.values, false, false);
  // Set range A12 on recipeList
  recipeList.getRange("A2").setFormulaLocal("=TRANSPOSE('Ingredients'!B2:D21)");
  // Paste to range A12 on recipeList from range A12:T14 on recipeList
  recipeList.getRange("A2").copyFrom(recipeList.getRange("A2:T4"), ExcelScript.RangeCopyType.values, false, false);
  // Clear ExcelScript.ClearApplyTo.contents from range B2:D21 on selectedSheet
  selectedSheet.getRange("B2:D21").clear(ExcelScript.ClearApplyTo.contents);
  // Clear ExcelScript.ClearApplyTo.contents from range L2:L4 on selectedSheet
  selectedSheet.getRange("L2:L4").clear(ExcelScript.ClearApplyTo.contents);
}
 
Thanks in advance
Excel for web
Office Scripts
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 21, 2022

    Change this line:
    recipeList.getRange("A2").copyFrom(recipeList.getRange("A2:T4"), ExcelScript.RangeCopyType.values, false, false);
    to:
    recipeList.getRange("A1000000").getRangeEdge(ExcelScript.KeyboardDirection.up).getOffsetRange(1,0).copyFrom(recipeList.getRange("A2:T4"), ExcelScript.RangeCopyType.values, false, false);

Resources