Script in google sheet worked yesterday, but not today

AppSheet AppSheet Q&A
, , ,
1

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)]);
      }
    }
  }
}
2

First, please be aware that this is an AppSheet platform Community. There are likely only a few here that have experience with Google App Scripts and can help. You would be better served to post this is a dedicated Google App Script forum.

Having said that…

If the script was working and now its not, it must be then that the proper criteria is NOT being met. For anyone to be able to help, we need to know why you think the script is not working - i.e. what data do you feel met the criteria and should have been interacted with by the script but wasn’t?

What were your specific expected results that didn’t happen?

3

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”);
}
}
}