CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheetsmapping
willy
willy

Reputation: 43

How do I append values starting from specific column

I have a code that retrieves project ID from specific column and uses it on API call to get more data that needs to be appended starting from the last column with data.

Here is the code that reads above column and uses it for API call

function readDates() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var range = sheet.getRange("F2:F" + sheet.getLastRow()).getValues();
    var searchString = "Project";
    var contents = [];  
    var team_array = [];
for (var i = 0; i < range.length; i++) {
    var lastRow = sheet.getRange(2 + i, 1, 1, 8).getValues();
    var data = {'project_id': lastRow[0][3]};
    var options = {method: 'get',headers: {Authorization: 'Bearer ' + TKF_AUTH}};
    var url = TKF_URL + Endpoint + data.project_id + '/users?auth=' + TKF_AUTH 
    var response = UrlFetchApp.fetch(url, options);
    var team = JSON.parse(response);
    var content = team.data;
    team_array.push(content);
    contents = contents.concat(content);  
}

The response needs to be appended from column G, row 2 but this is what I find challenging to do and any help will be appreciated here. Take a look at my code blow does, which is not what I intended.

if (contents.length > 0 ) {
  var dstSheet = SpreadsheetApp.getActive().getSheetByName('Projects');
  var values = contents.map(e => ([e.id, e.first_name, e.last_name, e.display_name, e.email, e.user_type_id, e.role]));
  var new_values=values.filter(item=>{
   if(item[6] == 'Project Leader' || item[6] == 'Senior Project Leader')
    {
      return true
   }
 })
 dstSheet.getRange(7, 1, new_values.length, new_values[0].length).setValues(new_values);// How do I structure this part to ensure it starts seting values from column G
}Logger.log(contents.map)
}

Upvotes: 0

Views: 139

Answers (1)

Cooper
Cooper

Reputation: 64032

Is this what you're looking for:

function readDates() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var vA = sheet.getvA(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
  var w=sheet.getLastColumn();
  for (var i = 0;i<vA.length;i++) {
    var options = {method: 'get',headers: {Authorization: 'Bearer ' + TKF_AUTH}};
    var url = TKF_URL + Endpoint + vA[i][3] + '/users?auth=' + TKF_AUTH 
    var response = UrlFetchApp.fetch(url, options);
    var team = JSON.parse(response.getContentText());
    var v=vA.slice(0,7);
    vA[i]=v.concat(team.data);//assume team.data is an array
    if(vA[i].length>w){w=vA[i].length;}
  }
  sh.getRange(2,1,vA.length,w).setValues(vA);
}

It would be nice to see what the return data looks like and if possible could we create headers that we can use to place the data correctly in the extended columns.

Upvotes: 2

Related Questions