Forum Discussion

SargentSynergy's avatar
SargentSynergy
Copper Contributor
Jul 27, 2021
Solved

Office Scripts - Working With Multiple Worksheets

I'm trying to write a script which writes data, via Power Automate, to multiple worksheets inside one workbook. I'm new to office scripts, so I'm not sure if I can do this in one script or if I have to do a separate script for each worksheet. 

 

Here is an example of the code:

 

function main(workbook: ExcelScript.Workbook,
Title: string,
Type: string,
Problem: string,
Part: string,
Date: string,
Frequency: string,
IDNumber: string,
Comments: string,
Originator: string)
{ 
  let selectedSheet = workbook.getWorksheet("Issues");
  //Populate All The Data
  selectedSheet.getRange("B6")
    .setValue(Title);
  selectedSheet.getRange("D6")
    .setValue(Type);
  selectedSheet.getRange("F6")
    .setValue(Problem);
  selectedSheet.getRange("B8")
    .setValue(Part);
  selectedSheet.getRange("D8")
    .setValue(Date);
  selectedSheet.getRange("H8")
    .setValue(Frequency);
  selectedSheet.getRange("K8")
    .setValue(IDNumber);
  selectedSheet.getRange("B9")
    .setValue(Comments);
  selectedSheet.getRange("F8")
    .setValue(Originator);
    }
 
So that is the end of what I'm doing on the first worksheet, and it works perfectly, and now I want to do a similar thing on three more worksheets, in the same work book. Is there a way for me to add something to the code to start the process on the next worksheet? It won't let me use the "let" statement again to change the worksheet, which I thought might be how it worked, so I'm at a standstill now. 
 
Thank you
Office Scripts
  • SargentSynergy's avatar
    SargentSynergy
    Jul 27, 2021

    I figured this out... all you have to do is declare a different variable name for the next workbook. In the first block of code, which is in my original message, the variable is called "selectedsheet" and the statement looks like this: "let selectedSheet = workbook.getWorksheet("Issues");"
    What I did for the next worksheet is this: "let sheetproblems = workbook.getWorksheet("problems")" and followed up with the rest of the items. 

     

    The way I figured this out was to open a new Excel sheet and actually record a new script by bolding the text in cells on two separate worksheets. Then I just looked at the code that was automatically generated to see how it handled switching worksheets. 

     

    I hope someone finds this information useful in the future. 

2 Replies

  • SargentSynergy's avatar
    SargentSynergy
    Copper Contributor
    Jul 27, 2021

    I figured this out... all you have to do is declare a different variable name for the next workbook. In the first block of code, which is in my original message, the variable is called "selectedsheet" and the statement looks like this: "let selectedSheet = workbook.getWorksheet("Issues");"
    What I did for the next worksheet is this: "let sheetproblems = workbook.getWorksheet("problems")" and followed up with the rest of the items. 

     

    The way I figured this out was to open a new Excel sheet and actually record a new script by bolding the text in cells on two separate worksheets. Then I just looked at the code that was automatically generated to see how it handled switching worksheets. 

     

    I hope someone finds this information useful in the future. 

Resources