0

Here is the sample Spreadsheet: https://docs.google.com/spreadsheets/d/1wlO33EWcERa0yYL2xb3BINFt5f441p9MbcB2mPe8eZQ/edit?usp=sharing

I need to insert the text in cell D1 into every third cell in Column C.
You can see in Column E what I am trying to do, and I don't want to have to do it by hand.
Essentially, I need to put %C123H before each paired 88# and T#.
The 88 is an ID and the T# is a device linked to the 88 number. I can then upload the entire list as .txt file into our school inventory system.

0

I used this Apps Script and it worked:

function myFunction() {
  var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Destiny Upload');
  var firstRow=1;
  var column=3;
  var destColumn=4;
  var increment=2;
  var lastRow=sheet.getLastRow();
  var values=sheet.getRange(firstRow, column, lastRow,1).getValues();
  var array=[];
  var k=1;
  Logger.log(lastRow);
  for(var i=0;i<lastRow;i++){
    if(i%increment==0){
      array.push("%C123H");
      k++;
    }
     array.push(values[i][0]);
  }
  var outerArray = [];
  for(var j=0;j<array.length;j++){
    var tempArray = [];
    tempArray.push(array[j]);
    Logger.log(array[j]);
    Logger.log(tempArray);
    outerArray.push(tempArray);
  }
  sheet.getRange(1, destColumn, array.length, 1).setValues(outerArray);
}
| improve this answer | |
0

I added a sheet called "Erik Help" and entered the formula into cell D1:

=FLATTEN(FILTER({IF(A:A="","",$C$1),A:B},A:A<>""))

Essentially, I added a virtual column filled with the $C$1 value, then formed a virtual array with this virtual column first, followed by A:B. I used FILTER to get rid of null rows and then used FLATTEN to make a single list.

NOTE: The FLATTEN function is an as-yet-unofficial Google Sheets function.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.