How to copy a cells value if another cell is not blank in google sheets?

Question

Original Explaination:

I'm looking for some help please. I have a form that logs new content requests, and I want it to return a expected completion date. Currently, I have the queue in a separate tab, with queue length calculated based on a countif False for content being completed.

If column one, sheet 1 (New Requests) is not blank, I need it to copy and paste the current value in sheet 2, cell A1 (The current queue length), into sheet 1, column 2. Each new form response will affect the queue length, so I'd need this to be pasted as values.

Basically, I need IF Sheet1columnA<>Blank, copy-paste as values Sheet2A1 into Sheet1ColumnB, but I'm still figuring out app script so any help would be great please!

Let me know if you need any further information

UPDATE: Not yet resolved, but here is where I've gotten to:

  function attempt() {
  let ss = SpreadsheetApp.openById(" removed due to data protection ");
  let sheet1 = ss.getSheetByName("New Requests");
  let sheet2 = ss.getSheetByName("Current Queue");
   var data = sheet1.getDataRange().getValues();
 // Iterates request by each row
 data.forEach(function (row) {
// First check if column I is blank
   if (row[8].isblank){
// then, if column A is not blank
   if (!row[0].isBlank()){
// copy Current Queue A1
     let valToPaste = sheet2.getrange(1,1).getValue();
// defines where to paste - This is where I'm struggling
     sheet1.getrange(row[8]).setValue(valToPaste)
   };
 });
}
}

Effectively, I want it for each row in "New Requests", to check if column I is blank, and if so, check if column A is not blank. If A is not blank, the current value in "Current Queue" A1 is to be pasted into column I.

Let me know your thoughts, I think I'm getting close here!

ale13 ale13 · Accepted Answer · 2021-04-22T13:45:09

You could start by using a script similar to this:

function copyVals() {
  let ss = SpreadsheetApp.openById("ID");
  let sheet1 = ss.getSheetByName("Sheet1");
  let sheet2 = ss.getSheetByName("Sheet2");
  let colA = sheet1.getRange('A:A');
  if (!colA.isBlank()) {
    let valToPaste = sheet2.getRange(1,1).getValue();
    sheet1.getRange(1,2).setValue(valToPaste);
  }
}

In order to check if the wanted column is not empty, the isBlank method has been used. If the condition checks, the valToPaste, which represents the value from Sheet2!A1 is pasted into Sheet2!B1.

Note

You may need to add a for loop, depending on your exact needs.
Since you mentioned you are using a form, you may want to check Apps Script triggers.

Reference

Apps Script Range Class - isBlank().

How to copy a cells value if another cell is not blank in google sheets?

1 Answers

Note

Reference