Adding tab name in column while importing from Sheet to Sheet in Apps Script

Question

I've struggled with this for a while & have put it off because I've always been able to just edit the SOURCE spreadsheet and add an IF statement with the TABNAME.

Meaning, in an adjacent column, I'll write, if Cell = blank, do nothing, if not give me tab name.. then I'll simply include that column in my import.

After putting off the true issue for a while, I'm unable to edit the source sheet in this instance -- causing me to go down a rabbit hole of trying to alter this script.

Here's what I have:

• A script that imports sheet to sheet (prefer this over importrange):

  function scheduleimport() {
      //@NotOnlyCurrentDoc
    var values = SpreadsheetApp.openById('spreadsheetID').
    getSheetByName('SourceSheet').getRange('A1:N').getValues();
    SpreadsheetApp.getActive().getSheetByName('DestinationSheet').
    getRange(1,1,values.length,values[0].length).setValues(values);}
  

This works super well & I've lived off it for a while...

What I'm looking to accomplish is putting the TABNAME of the source sheet in the column right next to the import range (only in cells where the range has values - not the entire destination sheet).

I came across this script that uses the length of a specific column & places a value wherever designated. I tweaked it to include the TABNAME:

function fillColB() {
var s = SpreadsheetApp.getActiveSheet();
tabname = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
var data = s.getRange("a1:a").getValues();
var data_len = data.length;
for(var i=0; i<data_len; i++) {
  if(data[i][0].length == 0) {
    s.getRange(i+1,2).setValue("");
  } else {
    s.getRange(i+1,2).setValue(tabname);
  }
}
}

I tried tying these together - essentially, getting the length of the import, and adding in the TABNAME as a value next to the last column. I am struggling heavy with it though -- any guidance would be much appreciated.

Answer 1

How about this:

function scheduleimport() {
  const ss = SpreadsheetApp.openById('spreadsheetID');
  const sh1 = ss.getSheetByName("SourceSheet");
  const vs = sh1.getRange('A1:N' + sh1.getLastRow()).getValues();
  const sh2 = ss.getSheetByName('DestinationSheet');
  sh2.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
  sh2.getRange(1,sh2.getLastColumn() +1).setValue(sh1.getName());
}

I may have read you code incorrectly but for an import I would think that you might wish the source spreadsheet and the destination spreadsheet to be different. So this next one provides that functionality

function scheduleimport() {
  const ss = SpreadsheetApp.openById(gobj.globals.testsourceid);//ids are coming from global object in the active sheet
  const dss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("Sheet0");
  const vs = sh1.getRange('A1:N' + sh1.getLastRow()).getValues();  
  const sh2 = dss.getSheetByName('Sheet1');
  sh2.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
  sh2.getRange(1,sh2.getLastColumn() +1).setValue(sh1.getName());
}

Putting the sheet name into a newlast column on all rows of the transferred data set.

function scheduleimport() {
  const ss = SpreadsheetApp.openById(gobj.globals.testsourceid);//ids are coming from global object in the active sheet
  const dss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("Sheet0");
  const vs = sh1.getRange('A1:N' + sh1.getLastRow()).getValues();  
  const sh2 = dss.getSheetByName('Sheet1');
  sh2.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
  sh2.getRange(1,sh2.getLastColumn() +1,vs.length).setValue(sh1.getName());
}