Alternate Row Shading In Google Sheets

This article shows you how to apply shading to every alternate row in a google sheet. You can apply the shading by using Alternating Colors Feature or simple conditional formatting formula.
Alternatively, you can use Google app script to apply colors to alternate rows.

Using Alternating Colors Feature

  • Open Google Sheet and select the range where you want to apply alternating colors
  • Go to Format->Alternating colors.
  • Selected range will get colored by default colors, you can change the styles by selecting different formatting styles on the right sidebar.
  • You can click on Remove Alternating colors option at the bottom on sidebar if you want to remove the alternating colors.

Using Conditional Formatting

  • Open Google Sheet and select the range where you want to apply alternating colors, goto Format->Conditional formatting.
  • Select Custom Formula is on the sidebar in left and enter formula in textbox =MOD(ROW(),2)=0
  • Select Formatting style and click button Done.


Note:

If you want to apply shading to alternate columns instead of alternate rows, enter =MOD(COLUMN(),2)=0 instead.

Using Google Apps Script

Given code snippet apply colors to alternate rows using google app script

This function get an array of the whole range, step through each row and apply different colors to even and odd rows. Then at the end call use setBackgroundColors() to set the colors on entire range selected.

function alternateColors() {
  var ss          = SpreadsheetApp.getActiveSpreadsheet();

  var sh          = ss.getActiveSheet();
  
  var backgrounds = sh.getRange('A1:H20').getBackgrounds()
  
  for(var i=0; i<backgrounds.length; i++){
    if(i%2==0){
      for(var k=0;k<backgrounds[0].length;k++){
        backgrounds[i][k]='#ffffff'
      }
    }    
    else{
      for(var k=0;k<backgrounds[0].length;k++){
        backgrounds[i][k]='#e0f7fa'
      }
    }
      
  }
  
  sh.getRange('A1:H20').setBackgrounds(backgrounds);
}

Get YouTube Analytics Data With Google Apps Script

This article explain here steps to configure Google Sheet and Apps Script to get Youtube Analytics data like views, subscribersGained, estimatedRevenue, grossRevenue, cpm etc for all videos uploaded for the linked channel account.

1. Create a New Google Spreadsheet And Add Script

var CLIENT_ID     = 'clientid';
var CLIENT_SECRET = 'clientsecret';

function putInSheet(result) {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh=ss.getActiveSheet();

  sh.getDataRange().clearContent();
  sh.appendRow(["Video ID","views","subscribersGained","estimatedRevenue","grossRevenue","estimatedAdRevenue","adImpressions","cpm"]);
  
  sh.getRange(2,1,result.length, result[0].length).setValues(result);
}

//Authorizes and makes a request to the Youtube API.
function runScript() {
  var myChannels = YouTube.Channels.list('id', {mine: true});
  var channel = myChannels.items[0];
  var channelId = channel.id;
   
  var today = new Date();
  var oneMonthAgo = new Date();
  oneMonthAgo.setMonth(today.getMonth() - 1);
  var todayFormatted = Utilities.formatDate(today, 'UTC', 'yyyy-MM-dd')
  var oneMonthAgoFormatted = Utilities.formatDate(oneMonthAgo, 'UTC', 'yyyy-MM-dd');
  
  var service = getService();
  if (service.hasAccess()) {
    var videoIDs = getVideoIDs();
    var result = [];
    videoIDs.forEach(function(videoID){
      var url = "https://youtubeanalytics.googleapis.com/v2/reports" +
        "?ids=channel=="+ channelId +
          "&startDate="+ oneMonthAgoFormatted +"&endDate="+ todayFormatted +
            "&metrics=views,subscribersGained,estimatedRevenue,grossRevenue,estimatedAdRevenue,adImpressions,cpm" + 
            //"&metrics=views,subscribersGained" + 
               "&filters=video==" + videoID;
      
      var response = UrlFetchApp.fetch(url, {
        headers: {
          Authorization: 'Bearer ' + service.getAccessToken()
        }
      });
      var row = [videoID];
      row = row.concat(JSON.parse(response.getContentText()).rows[0]);
      result.push(row);
    });
    
    putInSheet(result);
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    
    Browser.msgBox(authorizationUrl)
  }
}

//Get playlist of the uploaded videos of the channel
function getVideoIDs() {
  var playlist = JSON.parse(YouTube.Channels.list("contentDetails", {mine: true}))
     .items[0].contentDetails.relatedPlaylists.uploads;
  
  var vids = [];
  var nextPage;
  do {
    var list = JSON.parse(YouTube.PlaylistItems.list("snippet", {playlistId : playlist, maxResults:50, pageToken:nextPage}));
    nextPage = list.nextPageToken;
    
    list.items.forEach(function(item) {
      vids.push(item.snippet.resourceId.videoId);
    });
  } while(nextPage);
  
  return vids;
}

//Reset the authorization state, so that it can be re-tested.
function reset() {
  getService().reset();
}

//Configures the service.
function getService() {
  return OAuth2.createService('Youtube')
      //Set the endpoint URLs.
      .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
      .setTokenUrl('https://accounts.google.com/o/oauth2/token')

      //Set the client ID and secret.
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)

      //Set the name of the callback function that should be invoked to complete the OAuth flow.
      .setCallbackFunction('authCallback')

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getUserProperties())

      //Set the scope and additional Google-specific parameters.
      .setScope('https://www.googleapis.com/auth/yt-analytics.readonly https://www.googleapis.com/auth/yt-analytics-monetary.readonly https://www.googleapis.com/auth/youtube https://www.googleapis.com/auth/youtubepartner')
      .setParam('access_type', 'offline')
      .setParam('approval_prompt', 'force')
      .setParam('login_hint', Session.getActiveUser().getEmail());
}

//Handles the OAuth callback.
function authCallback(request) {
  var service = getService();
  var authorized = service.handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('Success!');
  } else {
    return HtmlService.createHtmlOutput('Denied.');
  }
}

//Logs the redict URI to register in the Google Developers Console.
function logRedirectUri() {
  Logger.log(getService().getRedirectUri());
}

2. Get Script ID

Click on File->Project Properties, copy script id which we will use in later steps.

3. Adding OAuth2 Library

OAuth2 for Apps Script is a library for Google Apps Script that provides the ability to create and authorize OAuth2 tokens as well as refresh them when they expire.
This library is already published as an Apps Script, making it easy to include in your project. 

To add OAuth2 library to your script, do the following in the Apps Script code editor:

1. Click on the menu item “Resources > Libraries…”
2. In the “Find a Library” text box, enter the script ID

1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF 

and click the “Select” button.

3. Choose a version in the dropdown box (usually best to pick the latest version).
4. Click the “Save” button.

4. Enable Youtube Data API and YouTube Analytics API

In the Apps Script editor, click Resources > Advanced Google Services.

  1. Locate YouTube Data API and YouTube Analytics in the dialog and click the corresponding toggle, setting it to on.
  2. Click the Google Cloud Platform API Dashboard link in the box below the list of services:
  3. Enter “YouTube Data API v3” into the search box and click on the corresponding entry in the results, Click the Enable button.
  4. Similarly search YouTube Analytics API and Enable.
  5. Return to the Apps Script code editor and click the OK button in the Advanced Google Services dialog. 

5. Create OAuth2 Credentials

On the Credentials page, select Create credentials, then select OAuth client ID.

Select Web Application and set a name, In Authorized redirect URIs Enter below URL
https://script.google.com/macros/d/{Script ID}/usercallback

Replace {Script ID} with script ID in found in Step 2 which is 1SWqweb6L8TLFR0tikpFmgr2ndogU7s05SEqGppE_jgJ7gNY2wIj1W4us

Click on Create button, below screen will appear with Client ID and Client Secret, Copy and paste Client Id and Client Secret in the script and save.

Run the function runScript, it will open message box with a URL.

Copy this url and open this url in new tab.

This app isn’t verified screen will appear, here you need to authorize it.

It should display a Success after authorization.

Run the same function runScript again which will populate analytics data on sheet.

YouTube Channel And Video Stats Using App Script

Following script read video stats of a youtube video

Get Youtube Video Stats
function getYouTubeVideoStats() {
  var sh=SpreadsheetApp.getActiveSheet();  
  
  var videoID=sh.getRange('B3').getValue();
  var data=YouTube.Videos.list('snippet, statistics, contentDetails', {id: videoID})    
  
  var item=data.items[0];    
    
  var title=item.snippet.title
  var commentCount=item.statistics.commentCount;
  var viewCount=item.statistics.viewCount;
  var likeCount=item.statistics.likeCount;
  var dislikeCount=item.statistics.dislikeCount;
  var duration=item.contentDetails.duration
  
  sh.getRange('B5').setValue(title);
  sh.getRange('B6').setValue(commentCount);
  sh.getRange('B7').setValue(viewCount);
  sh.getRange('B8').setValue(likeCount);
  sh.getRange('B9').setValue(dislikeCount);
  sh.getRange('B10').setValue(duration);
}

Get Youtube channel details

Get Youtube Channel Stats
function getYouTubeChannelStats() {
  var sh=SpreadsheetApp.getActiveSheet();  
  
  var videoID=sh.getRange('B3').getValue();
  var data=YouTube.Channels.list('snippet, statistics', {id: videoID})    
  
  var item=data.items[0];    
    
  var title=item.snippet.title;
  var videoCount=item.statistics.videoCount
  var subscriberCount=item.statistics.subscriberCount;
  var viewCount=item.statistics.viewCount;
  var s=item.statistics.hiddenSubscriberCount;
  
  
  sh.getRange('B5').setValue(title);
  sh.getRange('B6').setValue(videoCount);
  sh.getRange('B7').setValue(subscriberCount);
  sh.getRange('B8').setValue(viewCount);
  sh.getRange('B9').setValue(s);

}

Managing Files in Google Drive App

This tutorial is basically about Google Drive App Script.

Firstly, App Script is basically Javascript in the cloud, you can write your own Javascript code and have it run on Google cloud and access Google services.

App Script is javascript so you probably already know the program syntax, it is not a new language to learn, but you need to learn the services Google have and how to use them.

However, one of the Google services is the Drive service which allows scripts to create, find and modify files and folders in google drive backup.

Here, we will learn how to start with app script and how to use app script to create files in the drive, delete it, check its properties and share.

So, if you wanna know more about apps script you can visit Google App Script page https://developers.google.com/apps-script/
Therefore, this is the main page of google app script documentation, it has all the details to get started with App Script.
Similarly, it has tutorials, all the news, and updates on all the new features of the product and has all the details about all the services and functions you can use.

Write your Script

To write your app script you need to go to https://script.google.com and click on New Script. Which will open a script editor page where you can write your scripts.

Script to Loop Through All Files in Google Drive Folder

In my Drive I have a folder ‘TestFolder’ which contains four image files as you can see in image below.

Google Drive with Apps Script

Following code loop through each image file in ‘TestFolder’ and write their names to script editor logger window.

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”]
function logFolderFileNames() {
var folder=DriveApp.getFolderById(‘1wxEV1uvBsy-u8HCIdaX64MESDvhS8zjt’);
var files=folder.getFiles();
while(files.hasNext()){
var file=files.next();
Logger.log(file.getName());
}
}
[/cc]

Hence, after executing the script you can see the output in log window

Move a File From One Folder to Another Folder

This function move file ‘Pic2.jpg’ from one folder to another folder.

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”]
function moveFile(){
//Source Folder which contains the file
var parentFolder=DriveApp.getFolderById(‘1wxEV1uvBsy-u8HCIdaX64MESDvhS8zjt’);

//file which need to be moved to another folder, suppose you need to move ‘Pic2.jpg’ file
var childFile=parentFolder.getFilesByName(‘Pic2.jpg’).next();

//Get Destination folder where file will be moved.
var destinationfolder=DriveApp.getFolderById(‘1dd0FiAgV2dVpma-Pw6XjwVdm1AuCeYlU’);

//move file to folder
destinationfolder.addFile(childFile);

//Remove the file from parent folder
parentFolder.removeFile(childFile)

}
[/cc]

Create Files in Drive

Now, let’s write script to create a file in drive, 
App Script has three methods to create files in Drive.

  • DriveApp.createFile(blob);
  • DriveApp.createFile(name, content);
  • DriveApp.createFile(name, content, mimeType)

Create File from Blob Result

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”]
// This will create an image file in root folder of drive and set its name as ‘File Create From Blob Result’, it uses the Google Map Service to create the image
var blob = Maps.newStaticMap().setCenter(‘Delhi, India’).getBlob();
DriveApp.createFile(blob).setName(‘File Create From Blob Result’);
[/cc]

Create File from two parameters, name and content

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”]
//This will create a new file in root folder of drive
var file=DriveApp.createFile(‘Drive File’, ‘This is test file created in google drive using app script’);
[/cc]

Create File using three parameters, name, content and mimeType

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”]
//This create a new html file in drive
var file=DriveApp.createFile(‘New File with three parameters’, ‘New File in drive create by passing three parameters, name, content and mimitype‘, MimeType.HTML);
[/cc]

Deleting Files From Drive

Following function send file trash folder.

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”]
function deleteFile() {
var file=DriveApp.getFileById(‘1QO1HVuldcRvKILAY2GVPXsTZFhbo6E-r’);
file.setTrashed(true);
}
[/cc]

Look through previous post Google Apps Script to create and delete folder in Google drive to delete files permanently.

Sharing Files

For sharing files you can use below methods.

I hope Now you can create/delete and share files in the Google Drive with App Script, Let me know for any query in comment box or email me at info@xcript.org

Thanks for Visiting!

Google Apps Script to create and delete folder

First of all, I would like to tell you this article is about Google Apps Script. Here, I am going to show you here how you can use Google apps script to create and delete folders from Drive.

Firstly, create a new folder in the root folder of Google drive

So, the following function creates a new folder name ‘New Folder‘ in the root directory of the Google Drive.

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”] function createFolder() { DriveApp.createFolder(‘New Folder’); } [/cc]

Secondly, create a sub-folder inside an existing folder of Google drive.

Because the following function can be used to create a subfolder inside an existing folder.

Therefore, it accesses the parent folder using folder id and then creates a subfolder inside that.

So, you can use this method If you have parent folder.

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”] function createSubFolder(){ var folder=DriveApp.getFolderById(‘1OLVKsgp5AY0rCgRYPTsHPB40qqG1dTs1’); folder.createFolder(‘Sub Folder’); } [/cc]

Thirdly, create a sub-folder inside an existing folder of Google drive if you know the name of the folder

Google Drive you can have multiple folders or files with same names, so if you use App Script to get folder using folder name then you will get collection of all folders which are having same name.

The following function get a collection of all folders having same names ‘New Folder‘.

Loop through each folder and create sub folder inside that

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”] function createSubFolder() { var folders=DriveApp.getFoldersByName(‘New Folder’); while(folders.hasNext()){ var folder=folders.next(); folder.createFolder(‘Sub Folder’); } } [/cc]

Delete the folder from google drive (Send to Trash)

The below code snippet delete the folder from Google Drive.

“Please not that it just send the folder to Trash, it does not delete the folder permanently.”

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”] function deleteFolder() { var folder=DriveApp.getFolderById(’12wY7rnnkBhXFm9ByTytECqCOdNjEBeRA’); folder.setTrashed(true); } [/cc]

Remove folder from google drive.

This method does not delete the folder. Because it just removes the folder from all of its parents. So it cannot be seen in Drive except by searching for it or using the “All items” view.

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”] function deleteFolder() { var folder=DriveApp.getFoldersByName(‘TestFolder’).next(); DriveApp.removeFolder(folder); } [/cc]

Send all folders with same names to trash

This code block deletes all folders in the root folder which are having same name ‘New Folder

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”] function deleteFolders() { var folders=DriveApp.getFoldersByName(‘New Folder’); while(folders.hasNext()){ var folder=folders.next(); folder.setTrashed(true); } } [/cc]

Permanently deleting any folder or file from google drive

You cannot delete file or folders permanently from Google Drive without activating advance Google services.

Therefore,  you need to enable the Drive API in the Google Cloud Platform first before deleting file or folder.
Likewise, the same method is used, whether you want to remove a file or folder. Certainly, you just need to pass the file id, if you need to remove the file or pass the folder id  if you need to remove the folder.”

[cc lang=”javascript” escaped=”true” width=”100%” theme=”blackboard” noborder=”1″ line_numbers=”off”] //Permanently Delete file from drive function removeFile(){ var fileId=’1etSGF3Op1hwlITUBKo9DRmHA2g5Ezl32uENzxOssokM’ Drive.Files.remove(fileId); } //Permanently delete folder from drive function removeFolder(){ var folderId=’1HEWiaLCG74bumbbx2AxHNGMVI2G4PSU’ Drive.Files.remove(folderId); } [/cc]

Enable Drive API:

If you have any query about Google apps script. Then you can use this phrase “google apps script comment” in the comment section and get a quick response.

Inserting check boxes in Google sheets

A checkbox that permits the user can create binary choice. Like user can select choice between two options; options are yes or No. Checkboxes in Google Sheets is a new feature; You can add boxes to cells in a spreadsheet easily.  Checkboxes have shown as ☐ when unchecked, or ☑ when checked. Checkboxes can be used for multi-purpose. For tracking project, taking attendance and so on. Steps of inserting checkboxes are following:

Firstly, on your computer, open a spreadsheet in Google sheet  
You can check Google sheets templates in feature image in case of any confusion.

Secondly, select the cells you want to have check boxes

Thirdly, in the menu at the top, click Insert->Checkbox.

Moreover, to remove checkboxes, select the boxes you want to remove and press Delete.

App script to insert checkboxes

However, you can use app script to insert checkboxes, SpreadsheetApp class has new data validation() and requireCheckbox() methods which can be used.

Firstly, create a data validation rule. And apply that rule to any cell or range where you want to insert checkboxes.

Data validation rule
var checkboxes = SpreadsheetApp.newDataValidation().requireCheckbox().setAllowInvalid(false).build();

Secondly Apply validation to range A1: A10 and set values. As true if you want all checkboxes by default checked or false if you want all unchecked.

sh.getRange('A1:A10').setDataValidation(checkboxes).setValue(true);

Complete code snipped which is inserting checkboxes in cells A1:A10, you can set any other range or cell where you want to have check boxes.

function insertCheckbox(){

    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var sh=ss.getActiveSheet(); //Active Sheet
    
    //Data Validation Rule
    var checkboxes = SpreadsheetApp.newDataValidation().requireCheckbox().setAllowInvalid(false).build();

    //Set validation with checkboxes all checked

    sh.getRange('A1:A10').setDataValidation(checkboxes).setValue(true);

}

Consequently, now you are able to insert checkboxes in google sheets app. However, if you have any query regarding this you can ask through comment.

Remove Duplicates Google Sheets With App Script

Firstly In this article, you can learn how you Remove Duplicate Google Sheets with the help of App script. So, this article explains how you can write a simple script in google sheets to remove duplicate entries.
Therefore, large spreadsheets sometimes may have duplicate entries. Similarly, these duplicates can cause lots of problems in your data. Therefore, before using this data it makes sense to remove all duplicate entries.

Hence, there are two ways to remove duplicate entries in google sheets.

  • Firstly, using the built-in function: UNIQUE()
  • Secondly, using Google App Script.

Remove Duplicate in Google Sheets

Using Unique Function

Many people install add-ons to remove duplicates from sheets. But actually, Google sheet has built-in formula function which can remove duplicates. So, you can remove duplicates in google sheets using function Unique().

Note:

The unique function actually does not remove duplicates from the original data range. It just filters out unique items from selected range and populates to cells where the formula is applied.
So, you can replace original data with unique values populated by copy and paste as values

Example: Let’s say you want to get unique items from column A only, row number 2 to 30 and populate this in I column from 2nd row.

In cell I2 you will write formula as: =UNIQUE(A2:A30)

Remove Duplicate

So, you can Also read: Managing Files in Google Drive App

Likewise, if you want to have unique rows for entire range A2: G30. Therefore, you need to pass this range as a parameter in the Unique function.

Using App Script

Here, you can also use App Script to remove duplicates from sheets. Because App script is basically javascript. We need to write a javascript in script editor of the sheet.
Furthermore, you can go to the script editor by clicking Tools->Script editor

So, following function get unique items from range A2: A30 on active sheet and populates from cell I2

function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getRange('A2:G30').getValues()
var newData = new Array();
for(i in data){
var row = data[i];
var duplicate = false;
for(j in newData){
if(row.join() == newData[j].join()){
duplicate = true;
}
}
if(!duplicate){
newData.push(row);
}
}

sheet.getRange(2, 9, newData.length, newData[0].length).setValues(newData);
}

But, if you want to replace original data with unique rows you need to use this code.

function removeDuplicates(){ 
 var sheet = SpreadsheetApp.getActiveSheet(); 
 var data = sheet.getRange('A2:G30').getValues();
 var newData = new Array(); 
 for(i in data){
 var row = data[i]; var duplicate = false; 
 for(j in newData){ 
  if(row.join() == newData[j].join()){ 
   duplicate = true; 
  } 
 } 
 if(!duplicate){ 
  newData.push(row); 
 } 
 } 
 sheet.getRange('A2:G30').clearContent() 
 sheet.getRange(2, 1, newData.length, newData[0].length).setValues(newData);
} 

Google Script To Extract Text from PDF

In this article, you can read how you can Google script to extract text from PDF. Google Script can be used to extract text from a pdf document. And retain simple formatting. You can have the pdf in google drive folder or can be accessed from web URL.

The following snippet is extracting text from pdf and creating a new document with that text in root folder of drive.

function getTextFromPDF() {
var pdfFile = DriveApp.getFilesByName("sample.pdf").next();
var blob = pdfFile.getBlob();

var resource = {
title: blob.getName(),
mimeType: blob.getContentType()
};

//Create a Doc file in Google Drive with Extracted Text From Pdf
var docFile = Drive.Files.insert(resource, blob, {ocr: true, ocrLanguage: "en"});
}

Here Google Drive API works as OCR Engine to extract text from pdf. You need to first enable the advanced Drive API Service before running the script.

Read More about Google Apps Script to create and delete folder

Steps to enable API Service.
Goto Resource->Advanced Google Services and enable the Drive API as image below.

Thanks for reading this article I hope it will help you to get Google Script To Extract Text from PDF. Please comment if you have to face any problem related to an article.

Create Table in Google Doc Using Google Apps Script

Here we will learn how to create a table inside a Google document using Google apps script, This is a straightforward code which creates a table in Google Document.

We need to write a function. Likewise, I have created a function and called this createTable().

function createTable(){
 //Get Active Document
 var doc=DocumentApp.getActiveDocument();

 //Get Document Body
 var body=doc.getBody();

 //Add Table
 var table=body.appendTable();

 //Create 10 rows and 5 columns
 var numRows=10;
 var numColumns=5;

 for(var i=0;i < numRows;i++){
  var tr=table.appendTableRow();
  
  //Add cells in each row with blank values
  for(var j=0;j < numColumns;j++){
   var tb=tr.appendTableCell('');
  }
 }

}

You can run this script by selecting function name createTable from the drop down and then click on play button as highlighted in Script in below image.

Moreover, it will create a table with blank values in each cell.

You can also apply styles to table cells. Rather, below code is applying light grey color to the cell, font bold, foreground color black and horizontal alignment left.

  var cellFormatting = {}; 
  cellFormatting[DocumentApp.Attribute.BACKGROUND_COLOR] = '#fce5cd'; 
  cellFormatting[DocumentApp.Attribute.BOLD] = true; 
  cellFormatting[DocumentApp.Attribute.FOREGROUND_COLOR] = '#000000'; 
  cellFormatting[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.LEFT 

After adding this cell formatting code to create table function the entire code will look as below

function createTable(){
  var cellFormatting = {}; 
  cellFormatting[DocumentApp.Attribute.BACKGROUND_COLOR] = '#fce5cd'; 
  cellFormatting[DocumentApp.Attribute.BOLD] = true; 
  cellFormatting[DocumentApp.Attribute.FOREGROUND_COLOR] = '#000000'; 
  cellFormatting[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.LEFT 
  
  //Get Active Document
  var doc=DocumentApp.getActiveDocument();

  //Get Document Body
  var body=doc.getBody();

  //Add Table
  var table=body.appendTable();

  //Create 10 rows and 5 columns
  var numRows=10;
  var numColumns=5;

  for(var i=0;i < numRows;i++){
    var tr=table.appendTableRow();
  
    //Add cells in each row with blank values
    for(var j=0;j < numColumns;j++){
      var td=tr.appendTableCell('Enter Value Here'); 
      td.setAttributes(cellFormatting);
    }
  }

}

Create Table On Google Doc from data on Google Spreadsheet

Google docs spreadsheet

Firstly, Open source spreadsheet, you can use  SpreadsheetApp.openById or SpreadsheetApp.openByUrl in script to open the sheet.

Secondly,  you need to get the sheet which is having the source data, Suppose, source data is on the tab ‘TableData’ and range address A1: D10.

function createTableFromSheet(){
//Open source spreadsheet having sample table to be imported on doc.
var ss=SpreadsheetApp.openById('1XfrbhxTmwiKc6il7Kl7uT4qgSK5J090sCwVbjQBAYzo'); //Replace this Id with your sheet Id
var sh=ss.getSheetByName('TableData');
var values=sh.getRange('A1:D10').getValues(); //Set your Data range here

//Get Active Document
var doc = DocumentApp.getActiveDocument();

//Get Body
var body = doc.getBody();

//Add table
var table = body.appendTable(values);
}

How To Create Google Spreadsheet



Google spreadsheet is an online spreadsheet app that lets users create and format spreadsheets and simultaneously work with other people.
If you have a Gmail account, you can create and share your spreadsheet with others, even with those who do not have a Gmail account. it offers a comprehensive set of standard google spreadsheet template, features, and functions similar to those found in other spreadsheet applications such as Microsoft Excel.
Google Sheets isn’t only for consumers: it’s used every day by businesses and schools to manage spreadsheet data. With the new Sheets API v4 and Sheets add-ons, that data can be accessed by code as well as users.

For more information on google sheets, click on this link