Is there any way to extract a value from particular cell and store it in another spreadsheet cell(everyday @midnight 12) using spreadsheet Appsscript?

Question

I'm aware that I can extract a cell entry at mentioned timestamp and send the value to my mail id like below.

function triggerMail() {
  // Fetch the monthly sales
  var toGoRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NetWorth").getRange("H741"); 
  var toGo = toGoRange.getValue();
  // Fetch the email address
  var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NetWorth").getRange("J2");
  var emailAddress = emailRange.getValue();
  var date = Utilities.formatDate(new Date(), "GMT+5.30", "dd/MM/yyyy")
  // Send Alert Email.
  var message = '[' + date + ']' + ' Still toGo Rs.' + Number((toGo).toFixed(2)) + ' this month'; // Second column
  var subject = 'Daily ToGo Report';
  MailApp.sendEmail(emailAddress, subject, message);
    
};

Is there any way to store the value of toGo in the spreadsheet cell?. So that I can get an entry in the spreadsheet every day (currently, I'm getting an email every day)

mshcruz · Accepted Answer · 2021-07-12T12:40:11.863

You can write the value to the sheet using setValue().

For example, if you want to write to cell A1, you can add this to the end of your function (possibly instead of the line that sends the email): SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NetWorth").getRange('A1').setValue(toGo);

Edit:

If you want to keep a log of a value each time the function is run, you can write it to the last row of a given column. For example, to log it to column A:

function logToGo() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NetWorth");
  const toGo = sheet.getRange("H741").getValue();

  // Find the last populated row in the specified column
  const logColumn = 1; // Number of the column to output "toGo" to. A is 1, B is 2, etc.
  let logColumnLastRow = sheet.getMaxRows();
  const data = sheet.getRange(1, logColumn, logColumnLastRow).getValues();
  while (data[logColumnLastRow - 1][0] === "" && logColumnLastRow > 0) {
    logColumnLastRow--;
  }

  // Write to the last row in the specified column
  sheet.getRange(logColumnLastRow+1, logColumn).setValue(toGo);
}

Thanks! but is there any way to keep a record of it?. Here, I see it overrides A1 in the next day. — srccode, Jul 12 '21 at 05:59
Yes, I've updated my answer to write the value to a different row every time the function runs. — mshcruz, Jul 12 '21 at 12:26

score 0 · Answer 2 · edited Jul 12 '21 at 12:23

Use the function setValue() (link to google's documentation).

So after your 4th line, use something like this:

var enterRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ENTER SHEET NAME").getRange("ENTER RANGE").setValue(toGo);

If you want to enter it in a different SpreadSheet altogether, use the following:

var enterRange = SpreadsheetApp.openById("ENTER SPREADSHEET ID").getSheetByName("ENTER SHEET NAME").getRange("ENTER RANGE").setValue(toGo);

If you don't know, how to find the spreadsheet id, here is a red arrow pointing to it.

Is there any way to extract a value from particular cell and store it in another spreadsheet cell(everyday @midnight 12) using spreadsheet Appsscript?

2 Answers2