Dear programming Community,

at first I need to state, that I am not quite experienced in VBA and programming in general. I am currently trying to program a little function for a spread sheet. My aim is to check every cell in Column B (Topic) in the range from row 2 to 1000, whether or not its empty. If it is empty, a formular shall be inserted in the adjacend cell in Column A (Date), if it is not empty (topic is written), the Date in the adjacend cell (Column A) shall be copied and reinsertet just as the value. This is what I have so far, but unfortunately does not work. Could someone help me out here?

Thanks in advance and best regards, Harry

 function NeuerTest () {
  var ss=SpreadsheetApp.getActive();
  var s=ss.getSheetByName('Themenspeicher');
  var thema = s.getCell(i,2);
  var datum = s.getCell(i,1);
    for (i=2;i<=100;i  ) {
     if(thema.isBlank){
          }
      else {
        datum.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      }}
  }

CodePudding user response：

The suggested approach is to limit the calls to the Spreadsheet API, therefore instead of getting every cell, get all the data at once.

// this gets all the data in the Sheet
const allRows = s.getDataRange().getValues()

// here we will store what is written back into the sheet
const output = []

// now go through each row
allRows.forEach( (row, ind) => {

  const currentRowNumber = ind 1

  // check if column b is empty
  if( !row[1] || row[1]= "" ){
    // it is, therefore add a row with a formula
    output.push( ["=YOUR_FORMULA_HERE"] ) 

  } else {
    // keep the existing value
    output.push( [row[0]] )
  }
})

CodePudding user response：

Basically it could be something like this:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('A2:B1000');
  var data = range.getValues();

  for (let row in data) {
    if (data[row][1] == '') data[row][0] = '=YOUR_FORMULA';
  }

  range.setValues(data);
}

But actual answer depends on what exactly you have, how your formula looks like, etc. It would be better if you show a sample of your sheet (a couple of screenshots would be enough) 'before the script' and 'after the script'.