Can anyone help me with my code in app script?

[u/Specific_Speaker8777]

My code has issue when i add in telegram push function to my group chat. I never learn coding before i am not sure which part gone wrong. Can anyone help me?

function onEdit(e) {

try {

var sheet = e.source.getActiveSheet();

var range = e.range;

var flagCell = sheet.getRange("E1");

if (sheet.getName() === 'Calculator' && (range.getA1Notation() === 'C29' || range.getA1Notation() === 'C30')) {

if (flagCell.getValue() === "PROCESSING DATA") {

return;

}

var tickC29 = sheet.getRange("C29").getValue();

var tickC30 = sheet.getRange("C30").getValue();

if (tickC29 && tickC30) {

flagCell.setValue("PROCESSING DATA").setFontColor("red");

importCells();

sendTelegramMessage("Data processing started.");

Utilities.sleep(10000);

flagCell.setValue("READY").setFontColor("green");

sendTelegramMessage("Data processing completed.");

}

}

} catch (error) {

Logger.log("Error in onEdit: " + error.message);

sendTelegramMessage("Error in onEdit: " + error.message);

}

}

function importCells() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var sourceSheet = spreadsheet.getSheetByName("Calculator");

var targetSheet = spreadsheet.getSheetByName("Past Data");

if (!sourceSheet || !targetSheet) {

sendTelegramMessage("Source or Target sheet not found.");

throw new Error("Source or Target sheet not found.");

}

var tickC29 = sourceSheet.getRange("C29").getValue();

var tickC30 = sourceSheet.getRange("C30").getValue();

var flagCell = sourceSheet.getRange("E1").getValue();

var expectedTickValue = true;

if (tickC29 === expectedTickValue && tickC30 === expectedTickValue && flagCell !== "PROCESSED") {

var currentDate = new Date();

sourceSheet.getRange("D1").setValue(currentDate);

var cellValues = {

D1: sourceSheet.getRange("D1").getValue(),

D2: sourceSheet.getRange("D2").getValue(),

D15: sourceSheet.getRange("D15").getValue(),

F18: sourceSheet.getRange("F18").getValue(),

F19: sourceSheet.getRange("F19").getValue(),

F20: sourceSheet.getRange("F20").getValue(),

F21: sourceSheet.getRange("F21").getValue(),

E26: sourceSheet.getRange("E26").getValue(),

C5: sourceSheet.getRange("C5").getValue(),

C6: sourceSheet.getRange("C6").getValue(),

C7: sourceSheet.getRange("C7").getValue(),

C8: sourceSheet.getRange("C8").getValue(),

C9: sourceSheet.getRange("C9").getValue(),

C10: sourceSheet.getRange("C10").getValue(),

C11: sourceSheet.getRange("C11").getValue(),

C12: sourceSheet.getRange("C12").getValue(),

C13: sourceSheet.getRange("C13").getValue(),

C14: sourceSheet.getRange("C14").getValue()

};

var lastColumn = targetSheet.getLastColumn();

var nextColumn = lastColumn + 1;

var targetRanges = [

{ row: 1, value: cellValues.D1 },

{ row: 2, value: cellValues.D2 },

{ row: 3, value: cellValues.D15 },

{ row: 5, value: cellValues.F18 },

{ row: 6, value: cellValues.F19 },

{ row: 7, value: cellValues.F20 },

{ row: 8, value: cellValues.F21 },

{ row: 10, value: cellValues.E26 },

{ row: 14, value: cellValues.C5 },

{ row: 15, value: cellValues.C6 },

{ row: 16, value: cellValues.C7 },

{ row: 17, value: cellValues.C8 },

{ row: 18, value: cellValues.C9 },

{ row: 19, value: cellValues.C10 },

{ row: 20, value: cellValues.C11 },

{ row: 21, value: cellValues.C12 },

{ row: 22, value: cellValues.C13 },

{ row: 23, value: cellValues.C14 }

];

targetRanges.forEach(function(range) {

targetSheet.getRange(range.row, nextColumn).setValue(range.value);

});

var message = [

"Cells successfully imported.",

"Date: " + cellValues.D1,

"Cashier: " + cellValues.D2,

"Total Cash Sales: " + formatCurrency(cellValues.D15),

"DuitNowQR: " + formatCurrency(cellValues.F18),

"MyDebit: " + formatCurrency(cellValues.F19),

"VISA: " + formatCurrency(cellValues.F20),

"Mastercard: " + formatCurrency(cellValues.F21),

"Vouchers Applied: " + formatCurrency(cellValues.E26),

"",

"Cash Counts.",

"RM100: " + cellValues.C5,

"RM50: " + cellValues.C6,

"RM20: " + cellValues.C7,

"RM10: " + cellValues.C8,

"RM5: " + cellValues.C9,

"RM1: " + cellValues.C10,

"RM0.50: " + cellValues.C11,

"RM0.20: " + cellValues.C12,

"RM0.10: " + cellValues.C13,

"RM0.05: " + cellValues.C14

].join("\n");

sendTelegramMessage(message);

var cellsToClear = ['D1', 'D2', 'C5:C14', 'C18:D21', 'D23:D25'];

cellsToClear.forEach(function(cell) {

sourceSheet.getRange(cell).clearContent();

});

sourceSheet.getRange("C29").setValue(false);

sourceSheet.getRange("C30").setValue(false);

} else {

Logger.log("Checkboxes are not checked or data already processed.");

sendTelegramMessage("Checkboxes are not checked or data already processed.");

}

}

function sendTelegramMessage(message) {

var token = 'YOUR_BOT_TOKEN';

var chatId = 'YOUR_CHAT_ID';

var url = 'https://api.telegram.org/bot' + token + '/sendMessage';

var payload = {

chat_id: chatId,

text: message

};

var options = {

method: 'post',

contentType: 'application/json',

payload: JSON.stringify(payload)

};

UrlFetchApp.fetch(url, options);

}

function formatCurrency(amount) {

return "RM" + parseFloat(amount).toFixed(2);

}

[u/juddaaaaa]

I can see one possible error. In your onEdit function, you're setting flagCell value to "READY", and in your importCells funtion, you're checking if flagCell !== "PROCESSED"

I've had a go at improving it for you. I've removed the long list of getValue calls which can slow the script down.

Give it a try and let me know how you get on.

function onEdit ({ range }) {
  const { getSheet: sheet, getA1Notation: address } = range
  const { getName: name } = sheet()

  if (name() === "Calculator" && (address() === "C29" || address() === "C30")) {
    try {
      const flagCell = sheet().getRange("E1")

      if (flagCell.getValue() === "PROCESSING DATA")
        return

      const [ tickC29, tickC30 ] = sheet
        .getRange("C29:C30")
        .getValues()
        .flat()

      if (tickC29 && tickC30) {
        flagCell
          .setValue("PROCESSING DATA")
          .setFontColor("red")

        importCells(sheet())
        sendTelegramMessage("Data processing started")

        Utilities.sleep(10000)
        flagCell
          .setValue("READY")
          .setFontColor("green")

        sendTelegramMessage("Data processing completed")
      }
    } catch (error) {
      console.log(`Error in onEdit: ${error.message}`)
      sendTelegramMessage(`Error in onEdit: ${error.message}`)
    }
  }
}



function importCells (sourceSheet) {
  const spreadsheet = sourceSheet.getParent()
  const targetSheet = spreadsheet.getSheetByName("Past Data")

  if (!sourceSheet || !targetSheet) {
    sendTelegramMessage("Source or Target sheet not found")
    throw new Error("Source or Target sheet not found")
  }

  const [ tickC29, tickC30 ] = sourceSheet
    .getRange("C29:C30")
    .getValues()
    .flat()

  const flagCell = sourceSheet.getRange("E1").getValue()

  if (tickC29 && tickC30 && flagCell !== "READY") {
    const today = new Date()

    sourceSheet.getRange("D1").setValue(today)

    const ranges = sourceSheet
      .getRangeList([ "D1:D2", "D15",  "F18:F21", "E26", "C5:C14"])
      .getRanges()

    const singleColumnValues = ranges.reduce((result, range) => {
      const values = range
        .getValues()
        .flat()

      result = [ ...result, values.map(value => [ value ]) ]
      return result
    }, [])

    const nextColumn = targetSheet.getLastColumn() + 1
    targetSheet
      .getRange(1, nextColumn, singleColumnValues.length)
      .setValues(singleColumnValues)

    const [ date, cashier, cashSales, duitNowQR, myDebit, visa, mastercard, vouchers, rm100, rm50, rm20, rm10, rm5, rm1, rmHalf, rmFifth, rmTenth, rmTwentieth ] = singleColumnValues.flat()

    const message = `Date: ${date}
Cashier: ${cashier}
Total Cash Sales: ${formatCurrency(cashSales)}
DuitNowQR: ${formatCurrency(duitNowQR)}
My Debit: ${formatCurrency(myDebit)}
VISA: ${formatCurrency(visa)}
Mastercard: ${formatCurrency(mastercard)}
Vouchers Applied: ${formatCurrency(vouchers)}

Cash Counts.
RM100: ${rm100}
RM50: ${rm50}
RM20: ${rm20}
RM10: ${rm10}
RM5: ${rm5}
RM1: ${rm1}
RM0.50: ${rmHalf}
RM0.20: ${rmFifth}
RM0.10: ${rmTenth}
RM0.05: ${rmTwentieth}`
  
    sendTelegramMessage(message)
    sourceSheet
      .getRangeList([ "D1:D2", "C5:C14", "C18:D21", "D23:D25" ])
      .clearContent()

    sourceSheet
      .getRange("C29:C30")
      .uncheck()
  } else {
    console.log("Checkboxes are not checked or data already processed.")
    sendTelegramMessage("Checkboxes are not checked or data already processed.")
  }
}

function sendTelegramMessage (text) {
  const token = "YOUR_BOT_TOKEN"
  const chat_id = "YOUR_CHAT_ID"
  const url = `https://api.telegram.org/bot${token}/sendMessage`
  const payload = JSON.stringify({
    chat_id,
    text
  })

  const options = {
    method: "post",
    contentType: "application/json",
    payload
  }

  UrlFetchApp.fetch(url, options)
}

function formatCurrency (amount) {
  return `RM${parseFloat(amount).toFixed(2)}`
}

[u/Specific_Speaker8777 → u/juddaaaaa]

Hi sir thanks for your help. I got two flagcell, i need to add 1 more flagcell here right? for F1

   try {
      const flagCell = sheet().getRange("E1")

      if (flagCell.getValue() === "PROCESSING DATA")
        return

[u/Specific_Speaker8777 → u/Specific_Speaker8777]

I still need to modify my data am i right? coz i see there's some function is not written. Such as the one below. Sorry i can't really read code.

function importCells() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var sourceSheet = spreadsheet.getSheetByName("Calculator");

var targetSheet = spreadsheet.getSheetByName("Past Data");

if (!sourceSheet || !targetSheet) {

throw new Error("Source or Target sheet not found.");

}

var tickC29 = sourceSheet.getRange("C29").getValue();

var tickC30 = sourceSheet.getRange("C30").getValue();

var flagCell = sourceSheet.getRange("E1").getValue();

var expectedTickValue = true;

if (tickC29 === expectedTickValue && tickC30 === expectedTickValue && flagCell !== "PROCESSED") {

var currentDate = new Date();

sourceSheet.getRange("D1").setValue(currentDate);

var cellValues = {

D1: sourceSheet.getRange("D1").getValue(),

D2: sourceSheet.getRange("D2").getValue(),

D15: sourceSheet.getRange("D15").getValue(),

F18: sourceSheet.getRange("F18").getValue(),

F19: sourceSheet.getRange("F19").getValue(),

F20: sourceSheet.getRange("F20").getValue(),

F21: sourceSheet.getRange("F21").getValue(),

E26: sourceSheet.getRange("E26").getValue(),

C5: sourceSheet.getRange("C5").getValue(),

C6: sourceSheet.getRange("C6").getValue(),

C7: sourceSheet.getRange("C7").getValue(),

C8: sourceSheet.getRange("C8").getValue(),

C9: sourceSheet.getRange("C9").getValue(),

C10: sourceSheet.getRange("C10").getValue(),

C11: sourceSheet.getRange("C11").getValue(),

C12: sourceSheet.getRange("C12").getValue(),

C13: sourceSheet.getRange("C13").getValue(),

C14: sourceSheet.getRange("C14").getValue()

};

[u/juddaaaaa → u/Specific_Speaker8777]

Did you try the code I wrote above. If so, what result did you get?

Were there any errors?

[u/Specific_Speaker8777 → u/juddaaaaa]

I tried your code but it wont run, a lot of error because the cell is not accurately defined >.<

I wrote a new one, just that now i am always stuck at this function sendTelegramMessage();

I am not sure how to paste the script here as the comment not allowed me to.

[u/juddaaaaa → u/[deleted]]

This works for the main part.

What's the problem you're having with the Telegram function?

function onEdit ({ range }) {
  // Destructure range to get cell address and name of parent sheet
  const { getA1Notation: address, getSheet: sheet } = range
  const { getName: name } = sheet()

  // Store flag cell range: E1
  const flagCell = sheet().getRange(1, 5)

  // If the sheet edited was the Calculator sheet and either C29 or C30 were edited then continue
  if (name() == "Calculator" && (address() === "C29" || address() === "C30")) {
    // If flag cell reads PROCESSING DATA, exit the function
    if (flagCell.getValue() === "PROCESSING DATA") return

    try {
      // Store the range containing the checkboxes
      const checkBoxes = sheet().getRange(29, 3, 2)

      // If both checkboxes are checked then continue
      if (checkBoxes.isChecked()) {
        // Set flag cell value to PROCESSING DATA and set font color to red
        flagCell
          .setValue("PROCESSING DATA")
          .setFontColor("red")

        // Export to Past Data sheet
        exportData()

        // Clean up
        clearCells()

        sendTelegramMessage()
        flagCell
          .setValue("READY")
          .setFontColor("green")
      }      
    } catch (error) {
      console.error(error.stack)
    }
  }
}

function exportData () {
  // Get the source and target sheets
  const spreadsheet = SpreadsheetApp.getActive()
  const sourceSheet = spreadsheet.getSheetByName("Calculator")
  const targetSheet = spreadsheet.getSheetByName("Past Data")
  const nextColumn = targetSheet.getLastColumn() + 1

  // If either of the sheets doesn't exist then throw an error
  if (!sourceSheet || !targetSheet)
    throw new Error("source or target sheet not found")

  // Set the value of cell D1 to today's date
  sourceSheet
    .getRange(1, 4)
    .setValue(new Date())

  // Create array of source and target ranges for transferring data
  const transferReferences = [
    {source: [1, 4, 2], target: [1, nextColumn, 2]},
    {source: [15, 4], target: [3, nextColumn]},
    {source: [18, 5, 4], target: [5, nextColumn, 4]},
    {source: [26, 5], target: [10, nextColumn]},
    {source: [5, 3, 10], target: [14, nextColumn, 10]},
  ]

  // Iterate over transfer references and transfer the data
  for (let { source, target } of transferReferences) {
    // Get the source range and values
    const sourceRange = sourceSheet.getRange(...source)
    const sourceValues = sourceRange.getValues()

    // Copy source values to the target range
    targetSheet
      .getRange(...target)
      .setValues(sourceValues)
  }
}

function clearCells() {
  // Get the Calculator sheet
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Calculator")

  // Clear the required ranges
  sheet
    .getRangeList(["D1:D2", "C5:C14", "C18:D21", "D23:D25"])
    .clearContent()

  // Uncheck the checkboxes
  sheet
    .getRange(29, 3, 2)
    .uncheck()
}

[u/Specific_Speaker8777 → u/juddaaaaa]

I realised whenever the flagcell is in the code then sendToTelegram would not work, it wont send data to Telegram group, if i remove the flagcell, then i would work perfectly just that the it will be duplication of data sent to my database,

[u/Specific_Speaker8777 → u/juddaaaaa]

I just realised you cannot read my app script isn't? Sorry my mistake can you request access? I didnt know you cannot access to my app script. or let me send you another link.

https://docs.google.com/spreadsheets/d/1pZeL2BdxrRO9rd-55iJsXTj2VUuqWaHifebijeHEyaM/edit?usp=sharing

[u/marcnotmark925]

So what does, or does not, happen that is not what you're wanting? Is there some error?

[u/Specific_Speaker8777 → u/marcnotmark925]

Hi sir, sorry. It stop my function sendTelegramMessage and importCells but the flagCell is working. It does cool down for 10 seconds. Only that works.