javascript - Excel Sharepoint TypeScript : how to change a text if a cell is not empty on this date? - Stack Overflow

admin管理员组

文章数量:1297037

I used to do VBA script but since we have some excel files that are on sharepoint, I do not find a way to achieve the same thing with typescript (or javascript?).

I have a file that show all our pick-up trucks and on which project they are at the moment. I have a column that quickly shows if a truck is available or not depending on the date. All columns have a date. I have attached a screenshot.

File image

After searching on the internet for answers on how to adapt my VBA script to TypeScript (or javascript?) I tried to frankencode this:

function main(workbook: ExcelScript.Workbook){
//get active worksheet
    let sheet = workbook.getActiveWorksheet();
    let namesRange: ExcelScript.Range = workbook.getActiveWorksheet().getRange("A1");
    let rowCount: number = namesRange.getRowCount();
    let colCount: number = namesRange.getColumnCount();
    let vals: string[][] = namesRange.getValues() as string[][];
    let text1: "Disponible";
    let text2: "Indisponible";
    

        for (let i = 4; i < colCount; i++){
          //loop through columns
          let a= 1;
          let b = 4;
          let today = new Date().toLocaleDateString("fr-CA");
          //sets now value as the cell with current date
          if (vals[a][i] = today) {
            // when the loop gets to current date then
          for (let j = 6; j<rowCount; j++){
              //loop through rows
                if(vals[j][i] === ""){
                  //if cells is empty, then it is available
                  sheet.getRangeByIndexes(j,b,1,1).setValue(text1) 
                  //writes Available in the correct row, forth column
                } else sheet.getRangeByIndexes(j, b,1,1).setValue(text2)
                //if the cell is not empty, then write Not Available in correct row, forth column


                }

              }

          }
            

          }

The code runs without errors. But also it does not seems to work properly.

What it is supposed to do is run a loop in the columns until it hits the current date. Then, loop through the rows to see if something is written for that pick-up that day. If the cell is empty, then it's available and change the text in a specific cell to say so and non available if the cell is not empty.

I used to do VBA script but since we have some excel files that are on sharepoint, I do not find a way to achieve the same thing with typescript (or javascript?).

I have a file that show all our pick-up trucks and on which project they are at the moment. I have a column that quickly shows if a truck is available or not depending on the date. All columns have a date. I have attached a screenshot.

File image

After searching on the internet for answers on how to adapt my VBA script to TypeScript (or javascript?) I tried to frankencode this:

function main(workbook: ExcelScript.Workbook){
//get active worksheet
    let sheet = workbook.getActiveWorksheet();
    let namesRange: ExcelScript.Range = workbook.getActiveWorksheet().getRange("A1");
    let rowCount: number = namesRange.getRowCount();
    let colCount: number = namesRange.getColumnCount();
    let vals: string[][] = namesRange.getValues() as string[][];
    let text1: "Disponible";
    let text2: "Indisponible";
    

        for (let i = 4; i < colCount; i++){
          //loop through columns
          let a= 1;
          let b = 4;
          let today = new Date().toLocaleDateString("fr-CA");
          //sets now value as the cell with current date
          if (vals[a][i] = today) {
            // when the loop gets to current date then
          for (let j = 6; j<rowCount; j++){
              //loop through rows
                if(vals[j][i] === ""){
                  //if cells is empty, then it is available
                  sheet.getRangeByIndexes(j,b,1,1).setValue(text1) 
                  //writes Available in the correct row, forth column
                } else sheet.getRangeByIndexes(j, b,1,1).setValue(text2)
                //if the cell is not empty, then write Not Available in correct row, forth column


                }

              }

          }
            

          }

The code runs without errors. But also it does not seems to work properly.

What it is supposed to do is run a loop in the columns until it hits the current date. Then, loop through the rows to see if something is written for that pick-up that day. If the cell is empty, then it's available and change the text in a specific cell to say so and non available if the cell is not empty.

• javascript
• typescript

Share Improve this question Follow edited Feb 11 at 21:13 Robert 8,6835454 gold badges129129 silver badges169169 bronze badges asked Feb 11 at 16:27 LilphaeLilphae 111 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You have one error that I can see on line 7. You are using the assignment operator instead of the equality operator in your if statement. You probably know this but if you want to compare two values you use either == or ===. This is an easy mistake to make.

if (vals[a][i] = today) {

should be:

if (vals[a][i] === today) {

https://developer.mozilla./en-US/docs/Web/JavaScript/Reference/Operators/Assignment

https://developer.mozilla./en-US/docs/Web/JavaScript/Reference/Operators/Equality

https://developer.mozilla./en-US/docs/Web/JavaScript/Reference/Operators/Strict_equality

本文标签：