Tag Archives: Google Apps Script

Google Apps Script

Google Apps Script – get tasks – part 026.

This tutorial solves this issue about how to parse a text document open by an Id and put the output into a Spreadsheet. The input text from the text document must have four signed number delimited with a comma like as regular expression variable named r from source code:

JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
function write() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var doc = DocumentApp.openById('123xyzTimp\oOBb|hH...');
  var text = doc.getBody().getText();
  var result = [];
  var r = /[+-]?\d+,[+-]?\d+,[+-]?\d+,[+-]?\d+/g
  while ((res = r.exec(text)) !== null) {
    //result.push([res[0]].toString());
    result.push([res[0]].toString().split(","));
    Logger.log(result);
    }
 
  //ss.getRange(row, column, numRows, numColumns)
  ss.getRange(ss.getLastRow() + 1, 1, result.length, 4).setValues(result);
}

Google Apps Script – set colors – part 024.

This tutorial will show you how to alternate the colors by row. The first part will select by variables the active Spreadsheet, the sheet named Sheet1 and the range area. The for loop will parse all rows by modulo and set a variable named range_input. The color are changed by the setBackgrounds.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// set alternate colors by Row
function alternate_Colors_byRow() {
  var spread_active = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spread_active.getSheetByName("Sheet1");
  var range_input = spread_active.getRange("A1:B50").getValues();
  for(var i=0; i<range_input.length; i++){
    if(i%2==0){
      for(var k=0;k<range_input[0].length;k++){
        range_input[i][k]='#ffffff'
      }
    }    
    else{
      for(var k=0;k<range_input[0].length;k++){
        range_input[i][k]='#0076b0'
      }
    }  
  }
  sheet.getRange('A1:B50').setBackgrounds(range_input);
}

This is… Read More »