Need help with this appscript to be fixed

[Hendrik Bezuidenhout]

I'm encountering an unexpected behavior with a script I'm using. It worked successfully yesterday, but today it's not functioning as expected. I've already run a debug on the code and found no apparent issues.


The script is designed to perform the following tasks:

Retrieve data from the "Price" sheet.
For all other sheets, extract data only from rows starting at row 2, specifically from columns B and C.
Subsequently, the script should insert this data into the respective sheets.

Additionally, it should locate the last rows in these sheets where there is a date in column B, but no data present in columns H and I.

If these conditions are met, the script should insert the data.
Current Challenge:

I'm puzzled as to why the script, which operated correctly yesterday, is now experiencing this issue. I've reviewed the code in detail and can't identify any apparent problems.

https://docs.google.com/spreadsheets...it?usp=sharing

function copyDataFromPriceToAllSheets() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var priceSheet = ss.getSheetByName("Price"); var sheetRanges = { "Centus": "B3:C3", "CentusBNB": "B3:C3", "Plus": "B2:C2", "Binc": "B4:C4", "Bincin": "B5:C5", "Bincpl": "B6:C6", "Bincru": "B7:C7", "Bincua": "B8:C8", "Bincome": "B9:C9", "BincomeBNB": "B9:C9" }; for (var sheetName in sheetRanges) { var sheet = ss.getSheetByName(sheetName); var range = sheetRanges[sheetName]; // Get the data from Price sheet using the specified range var data = priceSheet.getRange(range).getValues()[0]; // Remove the dollar sign from column H if (data[0]) { data[0] = parseFloat(data[0].replace('$', '')); } // Format column I as percentage (multiply by 100 and round to 2 decimal places) if (data[1]) { data[1] = (parseFloat(data[1]) * 100).toFixed(2) + "%"; } var lastRow = sheet.getLastRow(); // Get all data in columns B, H, and I var rangeData = sheet.getRange(2, 2, lastRow - 1, 3).getValues(); for (var i = 0; i < rangeData.length; i++) { var rowData = rangeData[i]; // Check if there is no data in columns H and I in the current row with a date if (rowData[0] !== "" && rowData[1] === "" && rowData[2] === "") { // Set the data in the sheet columns H and I on the current row with a date sheet.getRange(i + 2, 8, 1, 2).setValues([data.slice(0, 2)]); } } } }

[Tanaike]

I have to apologize for my poor English skill. Unfortunately, I cannot understand your question. In order to correctly understand your question, can you provide the sample input and output situations you expect? First, I would like to correctly understand your question.

[Hendrik Bezuidenhout]

Good morning Tanaike

My English is also not that good.

I did add and show in my google form in sheet "binc" what must happen, hope you understand it.

[Tanaike]

Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, from your reply, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you could forgive my poor English skill.

[Hendrik Bezuidenhout]

Ok , let me try to explain so you can try to understand.

1. In my file I have a few sheets, and all of them have have columns that are using the same structure.

2. Each sheet have the same amount of columns in

3. Each row have the same structure for values.

4. Then I have a sheet with the name price

5. Every morning I run a applications that send values to Price sheet with Columns A,B,C first row is headers, then 9 data rows.

5. Every day I want to use this values in my sheets.

6. I am using appscript to add the following info for each sheet.  Date, Token name and Were did I receive it from. so it is adding the data into file

6.1 A =Id
6.2 B =Date
6.3 C=Tokens receive
6.4 J=from where did I get it.

7. Then this columns are using formulas to add extra info
7.1 D=Total Tokens
7.2 K= USD per months
7.3 L= ZAR per months
7.4 M=USD per Year
7.5 N=ZAR per year
7.6 O=ZaR Total of all

8 Then when H an I gets info form "price" sheet then the following is also added
8.1 E= USD total
8.2 F= UAD for Today
8.3 G= ZAR for today

9.  This is the 2 columns that must get data from "Price "sheet every morning, when I run the script.
9.1 H=Token value get from Price B
9.2 I= % get from Price C

10. So it I add today say Binc" 5 rows, and run script, then it must get data for H from Price B4 and for I get data from Price C3 and add it to all 5 rows
10.1 So it must loo if data in B and no data in H and I, then it must add it.

11. Here are the sheets and what rows they must get from Price
11.01 "Centus": "B3:C3",
11.02 "CentusBNB": "B3:C3",
11.03 "Plus": "B2:C2",
11.04 "Binc": "B4:C4",
11.05 "Bincin": "B5:C5",
11.06 "Bincpl": "B6:C6",
11.07 "Bincru": "B7:C7",
11.08 "Bincua": "B8:C8",
11.09 "Bincome": "B9:C9",
11.10 "BincomeBNB": "B9:C9"

12. So when I then add new date the next day, it must use the ne price data, that will always be in the same rows
12.1 the day before must not change.


If you do not understand any point, ask me more about it.

[George Ghanem]

What issue are you having? Show a screenshot of the error for us to give you some advice.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ddb7da49-6ada-443b-ab1a-c758b39373ffn%40googlegroups.com.

[RAKESH SHARMA]

Hi…

Thank you so much for connecting with me…I want a script which auto update all linked objects of google slides….

Can you help me with that…???

Regards

Rakesh Sharma

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAA4K68YOHaSH5J9D2ZaP_gTP-V3Bga5ZVFOphYBfeVkxXT-dpw%40mail.gmail.com.

[Keith Andersen]

Hendrik,

I would suggest doing a console.log() at each expected output to ensure you are getting the expected results at each step.

your last line;

sheet.getRange(i + 2, 8, 1, 2).setValues([data.slice(0, -2)]);

actually console.logs to an empty array [[]]

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7df5c807-c06e-4521-a532-1d6f8e374e62n%40googlegroups.com.

[Hendrik Bezuidenhout]

Got it to work with this code 

function copyToAllSheets() {

  var priceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Price");

  var sheetRanges = {

    "Centus": "B3:C3",

    "CentusBNB": "B3:C3",

    "Plus": "B2:C2",

    "Binc": "B4:C4",

    "Bincin": "B5:C5",

    "Bincpl": "B6:C6",

    "Bincru": "B7:C7",

    "Bincua": "B8:C8",

    "Bincome": "B9:C9",

    "BincomeBNB": "B9:C9"

  };

  for (var sheetName in sheetRanges) {

    var range = sheetRanges[sheetName];

    var centusSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

    var values = priceSheet.getRange(range).getValues();

    var centusLastRow = centusSheet.getLastRow();

    var centusDate = centusSheet.getRange("B" + centusLastRow).getValue();

    var centusHValue = centusSheet.getRange("H" + centusLastRow).getValue();

    var centusIValue = centusSheet.getRange("I" + centusLastRow).getValue();

    if (centusDate && !centusHValue && centusIValue === "") {

      centusSheet.getRange("H" + centusLastRow).setValue(values[0][0].replace('$', ''));

      centusSheet.getRange("H" + centusLastRow).setNumberFormat("0.00000000");

       centusSheet.getRange("H" + centusLastRow).setFontWeight("bold");

      centusSheet.getRange("I" + centusLastRow).setValue(values[0][1]);

      centusSheet.getRange("I" + centusLastRow).setNumberFormat("+0.00%;-0.00%;0.00%");

      centusSheet.getRange("I" + centusLastRow).setFontWeight("bold");

    }

  }

}