Google Sheets – Macro to Put Data in Empty Cell Every Month

google sheetsgoogle-sheets-macro

My question
pick up date and write in the following empty line?
I want to copy a data from a fixed cell to a database and that it always comes under the last line.

For example, Data from cell A1 from sheet1 and write on sheet2 in D1 and then a month later the data from A1 from sheet1 write on sheet2 in D2 ectect.
The data from D1 must not change when we put de data in D2

I have a script but it does not work at all

/** @OnlyCurrentDoc */

function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('D1').activate();
spreadsheet.getCurrentCell().setFormula('=Blad1!U29');
spreadsheet.getActiveRangeList().setNumberFormat('0');
};

Best Answer

Copying the data from the fixed cell

This is the simple part, but you can do it slightly differently from what you have posted:

var spreadsheet = SpreadsheetApp.getSheetByName('Sheet1');
var cellValue = spreadsheet.getRange('A1').getValue();

This will do two things:

  1. Get the exact tab (sheet1) that you want to get the value from, rather than whichever tab was last active
  2. Capture that value without moving the active "cursor" around the sheet by grabbing that Cell object and storing the value within

For setting the next cell on Sheet2

My assumption is that you want to go down column D and find the last entered value. Then copy the new value below that? (let me know if this is incorrect) There are a few ways to do this, depending if you have empty cells in that column, if this will always be the lowest row on that sheet populated, etc. The following is a simple way if it's always the last row of data on the sheet:

var rowToUpdate = spreadsheet.getLastRow() + 1; //Gets the row after the last row on that sheet with data
spreadsheet.getRange("D" + rowToUpdate).setValue(cellValue); //Sets the value to the next row to the value we grabbed earlier
Related Topic