First Store data to phone when data connected to internet automatic store data to google sheet

PATIN_LABS · December 21, 2022, 2:53am

First Store data to phone when data connected to internet, automatic store data to google sheet .
maybe anyone can help me? because here very rarely internet signal.

Earn_Money_online · December 21, 2022, 3:01am

Step1:
Store data in tiny db that you want.

Step2:
use Network/internet component. and check if network is their and tiny db is not empty then update the google sheet and after update clear that tiny db.
simple.
Thanks and Regards
-Pixel Store

PATIN_LABS · December 24, 2022, 3:56am

can you give me example block?
thanks

PATIN_LABS · December 24, 2022, 5:59am

Please correct my Block.
data from tinyDB can’t store to Google Sheet…

Still-learning · December 24, 2022, 6:15am

Instead of sending data row by row , why don’t you send the whole data at once on clicking button to send or synchronise??? Don’t use appendRow function , try with wrtieRange function

PATIN_LABS · December 24, 2022, 3:46pm

can you correct my block?
sorry my english very bad…

Still-learning · December 24, 2022, 8:17pm

Test this aia V1

Sheet1 aia

SynchronizeGsheet.aia (6.8 KB)

Testing Sheet

If net connected, Text box values will be added to gsheet else saved in tinydb.

Once net connected immediately app will store all the values into the gsheet at once and clears the tinydb

Used Scripts

Used Script Code

function doGet(e) { 
  // Main function 
  var ss = SpreadsheetApp.openById(e.parameter.ID);
  var sh = ss.getSheetByName(e.parameter.SH);
  var fn = e.parameter.FN;
  var rg = sh.getDataRange().getValues();

if ( fn == 'writeRange' ) {
    var data = JSON.parse(e.parameter.DATA);
    var ref = sh.getRange(e.parameter.REF).setValues(data);
    return ContentService.createTextOutput('Success' );
  }

  else if ( fn == 'querySheet' ) {
    var sql = e.parameter.SQL;
    var hdr = e.parameter.HDR;
    if ( hdr == 0 ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation().replace('A1','A2');
    var qry = '=query(' + rgq + ';\"' + sql + '\";0)';
    } else if ( hdr == 1 ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
    var qry = '=query(' + rgq + ';\"' + sql + '\";1)';    }
    var ts = ss.insertSheet();
    var setQuery = ts.getRange(1,1).setFormula(qry);
    var getResult = ts.getDataRange().getValues();
    ss.deleteSheet(ts); 
    return ContentService.createTextOutput(JSON.stringify(getResult));
  }
}

As usaul this script taken from @TimAi2 website

If you dont want that automatic sequence numbering in ColA mean the workflow can be shorten further

UPDATE:

Suppose if you dont want the ID in ColA and want only the input data to go into sheet mean try this code

Used Script Code

function doGet(e) { 
  // Main function 
  var ss = SpreadsheetApp.openById(e.parameter.ID);
  var sh = ss.getSheetByName(e.parameter.SH);
  var fn = e.parameter.FN;
  var rg = sh.getDataRange().getValues();

//This function append the Row one by one..
  if ( fn == 'ApendRow' ) {
 var data = JSON.parse('[' + e.parameter.DATA + ']');
 var lr = sh.getLastRow();
 sh.insertRowAfter(lr);
    rowNum = lr + 1;
    sh.getRange(rowNum,1,1,data[0].length).setValues(data);
    return ContentService.createTextOutput('Success');
}
//This function adds tons of values at a time into gsheet
else if ( fn == 'writeRange' ) {
    var data = JSON.parse(e.parameter.DATA);
    var ref = sh.getRange(e.parameter.REF).setValues(data);
    return ContentService.createTextOutput('Success' );
  }
}

And test this aia V2 (look into Sheet2)

Sheet2 AIA

SynchronizeGsheet_1.aia (12.3 KB)

Once net connected, app will look for tinydb data. If found then every list value will append one by one without index method…

(PS: In the above sheet pls go through with Sheet2)

PATIN_LABS · December 26, 2022, 2:24pm

can you give me access GSheet?

Still-learning:

Test this aia V1

Sheet1 aia

SynchronizeGsheet.aia (6.8 KB)

Testing Sheet

If net connected, Text box values will be added to gsheet else saved in tinydb.

Once net connected immediately app will store all the values into the gsheet at once and clears the tinydb

Used Scripts
Used Script Code
function doGet(e) { 
  // Main function 
  var ss = SpreadsheetApp.openById(e.parameter.ID);
  var sh = ss.getSheetByName(e.parameter.SH);
  var fn = e.parameter.FN;
  var rg = sh.getDataRange().getValues();

if ( fn == 'writeRange' ) {
    var data = JSON.parse(e.parameter.DATA);
    var ref = sh.getRange(e.parameter.REF).setValues(data);
    return ContentService.createTextOutput('Success' );
  }

  else if ( fn == 'querySheet' ) {
    var sql = e.parameter.SQL;
    var hdr = e.parameter.HDR;
    if ( hdr == 0 ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation().replace('A1','A2');
    var qry = '=query(' + rgq + ';\"' + sql + '\";0)';
    } else if ( hdr == 1 ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
    var qry = '=query(' + rgq + ';\"' + sql + '\";1)';    }
    var ts = ss.insertSheet();
    var setQuery = ts.getRange(1,1).setFormula(qry);
    var getResult = ts.getDataRange().getValues();
    ss.deleteSheet(ts); 
    return ContentService.createTextOutput(JSON.stringify(getResult));
  }
}
As usaul this script taken from @TimAi2 website

If you dont want that automatic sequence numbering in ColA mean the workflow can be shorten further

UPDATE:

Suppose if you dont want the ID in ColA and want only the input data to go into sheet mean try this code
Used Script Code
function doGet(e) { 
  // Main function 
  var ss = SpreadsheetApp.openById(e.parameter.ID);
  var sh = ss.getSheetByName(e.parameter.SH);
  var fn = e.parameter.FN;
  var rg = sh.getDataRange().getValues();

//This function append the Row one by one..
  if ( fn == 'ApendRow' ) {
 var data = JSON.parse('[' + e.parameter.DATA + ']');
 var lr = sh.getLastRow();
 sh.insertRowAfter(lr);
    rowNum = lr + 1;
    sh.getRange(rowNum,1,1,data[0].length).setValues(data);
    return ContentService.createTextOutput('Success');
}
//This function adds tons of values at a time into gsheet
else if ( fn == 'writeRange' ) {
    var data = JSON.parse(e.parameter.DATA);
    var ref = sh.getRange(e.parameter.REF).setValues(data);
    return ContentService.createTextOutput('Success' );
  }
}
And test this aia V2 (look into Sheet2)

Sheet2 AIA

SynchronizeGsheet_1.aia (12.3 KB)

Once net connected, app will look for tinydb data. If found then every list value will append one by one without index method…

(PS: In the above sheet pls go through with Sheet2)

PATIN_LABS · December 26, 2022, 5:04pm

error,
and data cant store to Gsheet

Screenshot_20221227_120309

PATIN_LABS:

can you give me access GSheet?
Still-learning:
Test this aia V1

Sheet1 aia

SynchronizeGsheet.aia (6.8 KB)

Testing Sheet

If net connected, Text box values will be added to gsheet else saved in tinydb.

Once net connected immediately app will store all the values into the gsheet at once and clears the tinydb

Used Scripts
Used Script Code
function doGet(e) { 
  // Main function 
  var ss = SpreadsheetApp.openById(e.parameter.ID);
  var sh = ss.getSheetByName(e.parameter.SH);
  var fn = e.parameter.FN;
  var rg = sh.getDataRange().getValues();

if ( fn == 'writeRange' ) {
    var data = JSON.parse(e.parameter.DATA);
    var ref = sh.getRange(e.parameter.REF).setValues(data);
    return ContentService.createTextOutput('Success' );
  }

  else if ( fn == 'querySheet' ) {
    var sql = e.parameter.SQL;
    var hdr = e.parameter.HDR;
    if ( hdr == 0 ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation().replace('A1','A2');
    var qry = '=query(' + rgq + ';\"' + sql + '\";0)';
    } else if ( hdr == 1 ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
    var qry = '=query(' + rgq + ';\"' + sql + '\";1)';    }
    var ts = ss.insertSheet();
    var setQuery = ts.getRange(1,1).setFormula(qry);
    var getResult = ts.getDataRange().getValues();
    ss.deleteSheet(ts); 
    return ContentService.createTextOutput(JSON.stringify(getResult));
  }
}
As usaul this script taken from @TimAi2 website

If you dont want that automatic sequence numbering in ColA mean the workflow can be shorten further

UPDATE:

Suppose if you dont want the ID in ColA and want only the input data to go into sheet mean try this code
Used Script Code
function doGet(e) { 
  // Main function 
  var ss = SpreadsheetApp.openById(e.parameter.ID);
  var sh = ss.getSheetByName(e.parameter.SH);
  var fn = e.parameter.FN;
  var rg = sh.getDataRange().getValues();

//This function append the Row one by one..
  if ( fn == 'ApendRow' ) {
 var data = JSON.parse('[' + e.parameter.DATA + ']');
 var lr = sh.getLastRow();
 sh.insertRowAfter(lr);
    rowNum = lr + 1;
    sh.getRange(rowNum,1,1,data[0].length).setValues(data);
    return ContentService.createTextOutput('Success');
}
//This function adds tons of values at a time into gsheet
else if ( fn == 'writeRange' ) {
    var data = JSON.parse(e.parameter.DATA);
    var ref = sh.getRange(e.parameter.REF).setValues(data);
    return ContentService.createTextOutput('Success' );
  }
}
And test this aia V2 (look into Sheet2)

Sheet2 AIA

SynchronizeGsheet_1.aia (12.3 KB)

Once net connected, app will look for tinydb data. If found then every list value will append one by one without index method…

(PS: In the above sheet pls go through with Sheet2)

Still-learning · December 29, 2022, 6:17am

I think I was deployed with only me so it throwing error. Better you copy the script code , deploy with anyone option and with script url test the above aia. You will see the difference

system · January 28, 2023, 6:17am

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.