Update data in 1st sheet based on data from another sheet with google apps script via unique identifier

Question

I have two sheets in on spreadsheet.

The first sheet I get data from external API to see all upcoming classes which constantly updates via triggers.

id | class | start | finish | description 1 tig welding 7/6/2013 10:30 7/6/2013 12:30 class description 2 tig welding 7/8/2013 10:30 7/8/2013 12:30 class description 3 Serger Basics 7/8/2013 14:30 7/6/2013 16:30 class description

The second is a static classes list where I add a second column "category".

class | category tig welding metalworking Serger Basics textiles

I would like to check if the titles in both spreadsheets match, and if so add category to the first sheet, like:

I then serve that spreadsheet as JSON to 3rd party web app. Sounds like a lot of work, but haven't came up with any other solution. Here is the work in progress code with a note where I am stuck.

function listClasses(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0]; //list of all upcoming classes
 var sheet1 = ss.getSheets()[1]; //list of titles

 var data = sheet.getDataRange().getValues(); // read all data in the sheet
 var data1 = sheet1.getDataRange().getValues();

 for(n=1;n<data1.length;++n){
    var title1 = data1[n][0];
    var category = data1[n][1];

  // compare Check if title appears in column A of sheet 2.
  if (ArrayLib.find(data1, 0, title1) != -1) {
    // Yes it does, do something in sheet 0
    for( var row = data.length -1; row >= 0; --row )
      if (data[row][1] == title1){

          var id = data[row][0];
          var title = data[row][1];
          var start = data[row][3];
          var finish = data[row][4];
          var description = data[row][5];
          var category = data1[n][1];

  // and here is where I got stuck!
  data.appendRow([category]); 


}
        }

      }

    }

do you want this new row added at the bottom of your sheet or would you prefer to sort it before writing back to the sheet ? — Serge insas
I guess I want to clear the existing row that matches the classes title and add write back with updated data which is category. Does it make sense? — Yulia

Serge insas Serge insas · Accepted Answer · 2013-07-08T23:07:17

From your comments it seems that you want to add a column on the matching rows... You can do that quite easily at array level by simply assigning the value to the row array element when a match is found. The only thing to care about is that each row (ie each array representing a row) must have exactly the same length otherwise you won't be able to write it back to the sheet properly.

One detail you didn't mention is wether the category row is the last one or not and/or if you have a header on your spreadsheet with a column name for "category".

in this first code I will assume you have, then you could simply use

    data[row][6]=category;// because data[row][6] already exist in this array because the header defines a range width of (at least) 7 when you used getDataRange()
    ...
    // and after the loop just write back data to sheet like this
    sheet.getRange(1,1,data.length,data[0].length).setValues(data);

if the column "category" doesn't exist then you should add an element in each row on the first run from within the loop using the push method so that data[row][6] exist for each row... something like this :

if(data[row].length<7){data[row].push(' ')};// ensure that data[row][6] does exist

this line should be placed in the loop that iterates the data array (index "row")

It is a bit hard to go further with the few informations you gave, I hope I made the right guess.

Update data in 1st sheet based on data from another sheet with google apps script via unique identifier

1 Answers