search

OfficeDev / office-scripts-docs

Office Scripts Documentation
https://learn.microsoft.com/office/dev/scripts
Creative Commons Attribution 4.0 International
185 stars 49 forks source link

Office Scripts - Suggested Examples #574

Closed pelowski closed 1 year ago

pelowski commented 1 year ago

You asked on the page for other examples we'd like to see so here are a couple.

  1. It would be nice to see how to hide multiple non-contiguous rows at once in a table. I can only ever get the last column to hide. 
    sheet.getRange("D:D").setColumnHidden(true);
sheet.getRange("F:F").setColumnHidden(true);
sheet.getRange("J:J").setColumnHidden(true);
// Also tried the following
sheet.getRanges("D:D,J:J").getAreas().forEach(cols => cols.setColumnHidden(true));
  2. It would be great to understand how to dynamically populate a table column with table reference syntax. Something similar to the following with URL and LinkText columns. sheet.getRange("C2:C2").setFormulasLocal("=hyperlink([@URL], [@LinkText])");

  3. It would be awesome to see how to populate data without having to statically define data ranges.

    
/**
* It is based off of this Office Scripts tutorial:
* https://docs.microsoft.com/office/dev/scripts/tutorials/excel-tutorial
*/
function main(workbook: ExcelScript.Workbook) {
// This function sets up a separate worksheet with sample data.
let statesSheet = setUpStatesAndProvinces(workbook);
statesSheet.setName("States and Provinces");

// Create a table using the data range.
let newTable = workbook.addTable(statesSheet.getRange("A1:C65"), true);
newTable.setName("StatesProvinces");
newTable.getRange().getFormat().autofitColumns();
newTable.setPredefinedTableStyle('TableStyleLight1');
}

function setUpStatesAndProvinces(workbook: ExcelScript.Workbook): ExcelScript.Worksheet { // Create a new worksheet. let sheet = workbook.addWorksheet();

// Add data. let range = sheet.getRange("A1:C65"); range.setValues([ ["Abbv", "State/Province", "Country"], ["AL", "Alabama", "USA"], ["AK", "Alaska", "USA"], ["AZ", "Arizona", "USA"], ["AR", "Arkansas", "USA"], ["CA", "California", "USA"], ["CO", "Colorado", "USA"], ["CT", "Connecticut", "USA"], ["DE", "Delaware", "USA"], ["DC", "District of Columbia", "USA"], ["FL", "Florida", "USA"], ["GA", "Georgia", "USA"], ["HI", "Hawaii", "USA"], ["ID", "Idaho", "USA"], ["IL", "Illinois", "USA"], ["IN", "Indiana", "USA"], ["IA", "Iowa", "USA"], ["KS", "Kansas", "USA"], ["KY", "Kentucky", "USA"], ["LA", "Louisiana", "USA"], ["ME", "Maine", "USA"], ["MD", "Maryland", "USA"], ["MA", "Massachusetts", "USA"], ["MI", "Michigan", "USA"], ["MN", "Minnesota", "USA"], ["MS", "Mississippi", "USA"], ["MO", "Missouri", "USA"], ["MT", "Montana", "USA"], ["NE", "Nebraska", "USA"], ["NV", "Nevada", "USA"], ["NH", "New Hampshire", "USA"], ["NJ", "New Jersey", "USA"], ["NM", "New Mexico", "USA"], ["NY", "New York", "USA"], ["NC", "North Carolina", "USA"], ["ND", "North Dakota", "USA"], ["OH", "Ohio", "USA"], ["OK", "Oklahoma", "USA"], ["OR", "Oregon", "USA"], ["PA", "Pennsylvania", "USA"], ["RI", "Rhode Island", "USA"], ["SC", "South Carolina", "USA"], ["SD", "South Dakota", "USA"], ["TN", "Tennessee", "USA"], ["TX", "Texas", "USA"], ["UT", "Utah", "USA"], ["VT", "Vermont", "USA"], ["VA", "Virginia", "USA"], ["WA", "Washington", "USA"], ["WV", "West Virginia", "USA"], ["WI", "Wisconsin", "USA"], ["WY", "Wyoming", "USA"], ["AB", "Alberta", "CAN"], ["BC", "British Columbia", "CAN"], ["MB", "Manitoba", "CAN"], ["NB", "New Brunswick", "CAN"], ["NL", "Newfoundland and Labrador", "CAN"], ["NT", "Northwest Territory", "CAN"], ["NS", "Nova Scotia", "CAN"], ["NU", "Nunavut Territory", "CAN"], ["ON", "Ontario", "CAN"], ["PE", "Prince Edward Island", "CAN"], ["QC", "Quebec", "CAN"], ["SK", "Saskatchewan", "CAN"], ["YT", "Yukon Territory", "CAN"], ]); range.select();

return sheet; }


---
#### Document Details

⚠ *Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.*

* ID: 98c58cbf-aaf7-3d3a-48f4-ce9cebcf331c
* Version Independent ID: 74821d34-eab6-ec0f-7a9f-55aca81975d9
* Content: [Basic scripts for Office Scripts in Excel - Office Scripts](https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/excel-samples)
* Content Source: [docs/resources/samples/excel-samples.md](https://github.com/OfficeDev/office-scripts-docs/blob/main/docs/resources/samples/excel-samples.md)
* Product: **excel**
* Technology: **scripts**
* GitHub Login: @o365devx
* Microsoft Alias: **o365devx**
AlexJerabek commented 1 year ago

Thank you for the sample requests @pelowski. I'll assign myself to start looking into these and I'll get back to you across the week with solutions and follow-up questions.

AlexJerabek commented 1 year ago

Okay, while I look into the others, I have a question about the column hiding sample.

The script code you shared is working fine for me. Here's script, plus the before and after spreadsheet screenshots.


function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  const sheet = workbook.getActiveWorksheet();

  // Hide columns D, F, and J.
  sheet.getRange("D:D").setColumnHidden(true);
  sheet.getRange("F:F").setColumnHidden(true);
  sheet.getRange("J:J").setColumnHidden(true);
}

image

image

Could you please share the behavior you're seeing? And what platform is this on (Windows, web, or Mac)?

AlexJerabek commented 1 year ago

For sample request 2, is this what you had in mind?

Month Price Units Sold Total
Jan 45 5  
Feb 45 3  
Mar 45 6   
function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  const table = workbook.getTable("Profits");

  // Get the column names for columns 2 and 3.
  // Note that these are 1-based indices.
  const nameOfColumn2 = table.getColumn(2).getName();
  const nameOfColumn3 = table.getColumn(3).getName();

  // Set the formula of the fourth column to be that row's column 2 and 3 values.
  const combinedColumn = table.getColumn(4).getRangeBetweenHeaderAndTotal();
  combinedColumn.setFormula(`=[@[${nameOfColumn2}]]*[@[${nameOfColumn3}]]`)
}
Month Price Units Sold Total
Jan 45 5 225
Feb 45 3 135
Mar 45 6 270
AlexJerabek commented 1 year ago

And, finally, for sample request 3, is this dynamic range sizing what you're looking for? I want to verify these scripts are actually targeting what you had in mind before committing them to the docs.

function main(workbook: ExcelScript.Workbook) {
  // The getData call could be replaced by input from Power Automate or a fetch call.
  const data = getData();

  // Create a new worksheet and switch to it.
  const newWorksheet = workbook.addWorksheet("DataSheet");
  newWorksheet.activate();

  // Get a range matching the size of the data.
  const dataRange = newWorksheet.getRangeByIndexes(
    0,
    0,
    data.length,
    data[0].length);

  // Set the data as the values in the range.
  dataRange.setValues(data);
}

function getData(): string[][] {
  return [["Abbv", "State/Province", "Country"],
          ["AL", "Alabama", "USA"],
          ["AK", "Alaska", "USA"],
          ["AZ", "Arizona", "USA"],
          ["AR", "Arkansas", "USA"],
          ["CA", "California", "USA"],
          ["CO", "Colorado", "USA"],
          ["CT", "Connecticut", "USA"],
          ["DE", "Delaware", "USA"],
          ["DC", "District of Columbia", "USA"],
          ["FL", "Florida", "USA"],
          ["GA", "Georgia", "USA"],
          ["HI", "Hawaii", "USA"],
          ["ID", "Idaho", "USA"],
          ["IL", "Illinois", "USA"],
          ["IN", "Indiana", "USA"],
          ["IA", "Iowa", "USA"],
          ["KS", "Kansas", "USA"],
          ["KY", "Kentucky", "USA"],
          ["LA", "Louisiana", "USA"],
          ["ME", "Maine", "USA"],
          ["MD", "Maryland", "USA"],
          ["MA", "Massachusetts", "USA"],
          ["MI", "Michigan", "USA"],
          ["MN", "Minnesota", "USA"],
          ["MS", "Mississippi", "USA"],
          ["MO", "Missouri", "USA"],
          ["MT", "Montana", "USA"],
          ["NE", "Nebraska", "USA"],
          ["NV", "Nevada", "USA"],
          ["NH", "New Hampshire", "USA"],
          ["NJ", "New Jersey", "USA"],
          ["NM", "New Mexico", "USA"],
          ["NY", "New York", "USA"],
          ["NC", "North Carolina", "USA"],
          ["ND", "North Dakota", "USA"],
          ["OH", "Ohio", "USA"],
          ["OK", "Oklahoma", "USA"],
          ["OR", "Oregon", "USA"],
          ["PA", "Pennsylvania", "USA"],
          ["RI", "Rhode Island", "USA"],
          ["SC", "South Carolina", "USA"],
          ["SD", "South Dakota", "USA"],
          ["TN", "Tennessee", "USA"],
          ["TX", "Texas", "USA"],
          ["UT", "Utah", "USA"],
          ["VT", "Vermont", "USA"],
          ["VA", "Virginia", "USA"],
          ["WA", "Washington", "USA"],
          ["WV", "West Virginia", "USA"],
          ["WI", "Wisconsin", "USA"],
          ["WY", "Wyoming", "USA"],
          ["AB", "Alberta", "CAN"],
          ["BC", "British Columbia", "CAN"],
          ["MB", "Manitoba", "CAN"],
          ["NB", "New Brunswick", "CAN"],
          ["NL", "Newfoundland and Labrador", "CAN"],
          ["NT", "Northwest Territory", "CAN"],
          ["NS", "Nova Scotia", "CAN"],
          ["NU", "Nunavut Territory", "CAN"],
          ["ON", "Ontario", "CAN"],
          ["PE", "Prince Edward Island", "CAN"],
          ["QC", "Quebec", "CAN"],
          ["SK", "Saskatchewan", "CAN"],
          ["YT", "Yukon Territory", "CAN"]];
}
ghost commented 1 year ago

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

pelowski commented 1 year ago

@AlexJerabek, thank you for looking into all of these things!

The script code you shared is working fine for me. Here's script, plus the before and after spreadsheet screenshots.

I believe it has to do with the fact that I had this data in a table. If you don't have the data in a table the ability to hide columns seems to work but since the first thing I did was add the data to a table, I think that causes the hide operation to fail without appearing to fail. Here is a video of what I experienced. (Note: This is untrue... it's not because of the table. Please see what I discovered further below.)

pelowski commented 1 year ago

For sample request 2, is this what you had in mind?

Using your example here and moving the hiding of the columns outside of the table creation function and I was able to get things to work include the hiding of the column within the table.

function main(workbook: ExcelScript.Workbook) {
  // This function sets up a separate worksheet with sample data.
  let WonderfulLifeSheet = setupData(workbook);
  WonderfulLifeSheet.setName("Wonderful Life");

  // Create a table using the data range.
  let newTable = workbook.addTable(WonderfulLifeSheet.getRange("A1:J15"), true);
  newTable.setName("WonderfulLife");
  newTable.getRange().getFormat().autofitColumns();
  newTable.setPredefinedTableStyle('TableStyleLight1');

  // Get the column names
  const ActorName = newTable.getColumn(3).getName();
  const IMDBUrl = newTable.getColumn(9).getName();

  // Set the formula newTable the fourth column to be that row's column 2 and 3 values.
  const LinkedName = newTable.getColumn(10).getRangeBetweenHeaderAndTotal();
  LinkedName.setFormula(`=HYPERLINK([@[${IMDBUrl}]],[@[${ActorName}]])`)

  WonderfulLifeSheet.getRange("C:C").setColumnHidden(true);

}

function setupData(workbook: ExcelScript.Workbook): ExcelScript.Worksheet {
  // Create a new worksheet.
  let sheet = workbook.addWorksheet();

  // Add data.
  let range = sheet.getRange("A1:J15");
  range.setValues([
    ["First Name", "Last Name", "Actor", "Date of Birth", "Date of Death", "Gender", "Occupation", "Age", "URL", "Link"],
    ["George", "Bailey", "Jimmy Stewart", "5/20/1908", "7/2/1997", "Male", "Bank President", "89", "https://www.imdb.com/name/nm0000071", ""],
    ["Mary", "Hatch/Bailey", "Donna Reed", "1/27/1921", "1/14/1986", "Female", "Eternal Optimist", "65", "https://www.imdb.com/name/nm0001656", ""],
    ["Harry", "Bailey", "Todd Karns", "1/15/1921", "2/5/2000", "Male", "War Hero", "79", "https://www.imdb.com/name/nm0439851", ""],
    ["Billy", "Bailey", "Thomas Mitchell", "7/11/1892", "12/17/1962", "Male", "Derelict Financial Officer", "70", "https://www.imdb.com/name/nm0593775", ""],
    ["Clarence", "Odbody", "Henry Travers", "3/5/1874", "11/18/1965", "Male", "Angel", "91", "https://www.imdb.com/name/nm0871287", ""],
    ["Henry", "Potter", "Lionel Barrymore", "4/28/1878", "11/15/1954", "Male", "Questionable Lender", "76", "https://www.imdb.com/name/nm0000859", ""],
    ["Giuseppe", "Martini", "William Edmunds", "1/1/1885", "12/7/1981", "Male", "Bar Proprietor", "96", "https://www.imdb.com/name/nm0249602", ""],
    ["Emil", "Gower", "H.B. Warner", "10/26/1876", "12/21/1958", "Male", "Drugist", "82", "https://www.imdb.com/name/nm0912478", ""],
    ["Ernie", "Bishop", "Frank Faylen", "8/8/1905", "8/2/1985", "Male", "Cabbie", "80", "https://www.imdb.com/name/nm0269709", ""],
    ["Bert", "", "Ward Bond", "4/9/1903", "11/5/1960", "Male", "Police Officer", "57", "https://www.imdb.com/name/nm0000955", ""],
    ["Sam", "Wainwright", "Frank Albertson", "2/2/1909", "2/29/1964", "Male", "Entrepreneur", "55", "https://www.imdb.com/name/nm0007214", ""],
    ["Violet", "Bick", "Gloria Grahame", "11/28/1923", "10/5/1981", "Female", "Questionable", "57", "https://www.imdb.com/name/nm0002108", ""],
    ["Annie", "", "Lillian Randolph", "12/14/1898", "9/12/1980", "Female", "Maid", "81", "https://www.imdb.com/name/nm0709917", ""],
    ["Zuzu", "Bailey", "Karolyn Grimes", "7/4/1940", "", "Female", "Daughter", "81", "https://www.imdb.com/name/nm0342216", ""],
]);

  range.select();

return sheet;
}
pelowski commented 1 year ago

And, finally, for sample request 3, is this dynamic range sizing what you're looking for? I want to verify these scripts are actually targeting what you had in mind before committing them to the docs.

Yes, this is helpful. Thank you. I wasn't sure how to make a range dynamic like this and everything I saw had static ranges specified.

pelowski commented 1 year ago

I've changed the States and Provinces function to look like this and it helps me to understand what is going on better. It's a more practical and flexible example than what I originally found here. Thanks again!

function main(workbook: ExcelScript.Workbook) {

  // If the worksheet already exists, delete it
  if (workbook.getWorksheet("States and Provinces")) {
    console.log("Deleting the existing States and Provinces worksheet.")
    workbook.getWorksheet("States and Provinces").delete();
  }

  // The getData call could be replaced by input from Power Automate or a fetch call.
  const data = getData();

  // Create a new worksheet and switch to it.
  const statesSheet = workbook.addWorksheet("States and Provinces");
  statesSheet.activate();
  statesSheet.getFreezePanes().freezeRows(1);
  statesSheet.setTabColor("002060");

  // Get a range matching the size of the data.
  const dataRange = statesSheet.getRangeByIndexes(
    0,
    0,
    data.length,
    data[0].length);
  // Set the data as the values in the range.
  dataRange.setValues(data);

  // Create a table using the data range.
  let newTable = workbook.addTable(dataRange, true);
  newTable.setName("StatesProvinces");
  newTable.setShowFilterButton(false);
  dataRange.getFormat().autofitColumns();
  //dataRange.getFormat().setColumnWidth(120);
  newTable.setPredefinedTableStyle('TableStyleLight1');

  // Sort the table based on the first column.
  newTable.getSort().apply([
    {
      // '0' refers to the index, not the column name.
      key: 1,
      ascending: true,
    },
  ]);
}

function getData(): string[][] {
  return [["Abbv", "State/Province", "Country"],
  ["AL", "Alabama", "USA"],
  ["AK", "Alaska", "USA"],
  ["AZ", "Arizona", "USA"],
  ["AR", "Arkansas", "USA"],
  ["CA", "California", "USA"],
  ["CO", "Colorado", "USA"],
  ["CT", "Connecticut", "USA"],
  ["DE", "Delaware", "USA"],
  ["DC", "District of Columbia", "USA"],
  ["FL", "Florida", "USA"],
  ["GA", "Georgia", "USA"],
  ["HI", "Hawaii", "USA"],
  ["ID", "Idaho", "USA"],
  ["IL", "Illinois", "USA"],
  ["IN", "Indiana", "USA"],
  ["IA", "Iowa", "USA"],
  ["KS", "Kansas", "USA"],
  ["KY", "Kentucky", "USA"],
  ["LA", "Louisiana", "USA"],
  ["ME", "Maine", "USA"],
  ["MD", "Maryland", "USA"],
  ["MA", "Massachusetts", "USA"],
  ["MI", "Michigan", "USA"],
  ["MN", "Minnesota", "USA"],
  ["MS", "Mississippi", "USA"],
  ["MO", "Missouri", "USA"],
  ["MT", "Montana", "USA"],
  ["NE", "Nebraska", "USA"],
  ["NV", "Nevada", "USA"],
  ["NH", "New Hampshire", "USA"],
  ["NJ", "New Jersey", "USA"],
  ["NM", "New Mexico", "USA"],
  ["NY", "New York", "USA"],
  ["NC", "North Carolina", "USA"],
  ["ND", "North Dakota", "USA"],
  ["OH", "Ohio", "USA"],
  ["OK", "Oklahoma", "USA"],
  ["OR", "Oregon", "USA"],
  ["PA", "Pennsylvania", "USA"],
  ["RI", "Rhode Island", "USA"],
  ["SC", "South Carolina", "USA"],
  ["SD", "South Dakota", "USA"],
  ["TN", "Tennessee", "USA"],
  ["TX", "Texas", "USA"],
  ["UT", "Utah", "USA"],
  ["VT", "Vermont", "USA"],
  ["VA", "Virginia", "USA"],
  ["WA", "Washington", "USA"],
  ["WV", "West Virginia", "USA"],
  ["WI", "Wisconsin", "USA"],
  ["WY", "Wyoming", "USA"],
  ["AB", "Alberta", "CAN"],
  ["BC", "British Columbia", "CAN"],
  ["MB", "Manitoba", "CAN"],
  ["NB", "New Brunswick", "CAN"],
  ["NL", "Newfoundland and Labrador", "CAN"],
  ["NT", "Northwest Territory", "CAN"],
  ["NS", "Nova Scotia", "CAN"],
  ["NU", "Nunavut Territory", "CAN"],
  ["ON", "Ontario", "CAN"],
  ["PE", "Prince Edward Island", "CAN"],
  ["QC", "Quebec", "CAN"],
  ["SK", "Saskatchewan", "CAN"],
  ["YT", "Yukon Territory", "CAN"]];
}
AlexJerabek commented 1 year ago

Thanks for the follow-ups @pelowski. I've created PR #578 to add these three samples. Please let me know if you have any further questions or if I missed something in your replies.